How can i separate the record according to the Gender wise?
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
How can i separate the record according to the Gender wise?
Hello sir, I have stored all record on my first sheet. Now i want to separate the record of Male & Female on separate sheets. Will you please help me to how can it possible.
Thanks & regards,
Prince
Thanks & regards,
Prince
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How can i separate the record according to the Gender wi
Here is a macro that you can use:
Code: Select all
Sub SplitData()
' Create a criteria range
Sheet1.Range("E1") = "Gender"
Sheet1.Range("E2") = "M"
' Use Advanced Filter to copy the filtered data
Sheet1.Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheet1.Range("E1:E2"), _
CopyToRange:=Worksheets("Male").Range("A1:B1")
' Change the criteria
Sheet1.Range("E2") = "F"
' Copy the filtered data
Sheet1.Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheet1.Range("E1:E2"), _
CopyToRange:=Worksheets("Female").Range("A1:B1")
' Clear the criteria range
Sheet1.Range("E1:E2").Clear
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: How can i separate the record according to the Gender wi
Thank you Mr. Hansv, its working perfectly. Now m trying this macro code with attached file Boys and Girls sheet ( Marked with red color). But unfortunately i'm not able to modify your code.
Kindly suggest me where the code will be modified.
With regards,
Prince
Kindly suggest me where the code will be modified.
With regards,
Prince
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How can i separate the record according to the Gender wi
The layout of your "All Record" sheet is not suitable for filtering, since the row with field names ("Sr. No.", "Roll-No" etc.) consists of merged cells. The field name row must be a single row immediately above the data.
Since you now have data in E1 and E2, we can't use these cells as criteria range of course. We must use other cells.
The field names in the Girls and Boys sheets are now on row 3, not on row 1 as before.
You had switched the code for boys and girls in the code (I assume that F = female and M = male).
I have attached the workbook with the modified code.
Warning: if you change the layout again, the code won't work correctly anymore.
Since you now have data in E1 and E2, we can't use these cells as criteria range of course. We must use other cells.
The field names in the Girls and Boys sheets are now on row 3, not on row 1 as before.
You had switched the code for boys and girls in the code (I assume that F = female and M = male).
I have attached the workbook with the modified code.
Warning: if you change the layout again, the code won't work correctly anymore.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: How can i separate the record according to the Gender wi
Thank you mr. Hansv
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: How can i separate the record according to the Gender wi
Mr. Hansv , Kindly check the above attached file that you have attached. In this file boys worksheet providing accurate result but on Girls sheet showing the combine record of boys and girls.
Thanks & regards,
prince
Thanks & regards,
prince
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: How can i separate the record according to the Gender wi
In the girls filter, change the line:
to this line:
Code: Select all
CriteriaRange:=.Range("E1:E2"), _
Code: Select all
CriteriaRange:=.Range("N1:N2"), _
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How can i separate the record according to the Gender wi
Thanks, Rudi!
Prince, my apologies, I forgot to change the criteria range for girls. Rudi has explained how to correct it.
Also, please note that the macro is now in a standard module. Code such as this does not belong in a worksheet module.
Prince, my apologies, I forgot to change the criteria range for girls. Rudi has explained how to correct it.
Also, please note that the macro is now in a standard module. Code such as this does not belong in a worksheet module.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: How can i separate the record according to the Gender wi
Thank you Mr. Hans & Mr. Rudi , Today i have almost completed my task & its credit goes to both of you. Sir, above code is working very well . Now i want to attached this code on my actual worksheet. Is this work on this sheet or not? I have not changed the basic structure of this sheet.
Thanks & Regard,
Prince
Thanks & Regard,
Prince
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: How can i separate the record according to the Gender wi
Try this...
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: How can i separate the record according to the Gender wi
Will you please tell me why in SplitData macro code is not working on the worksheet in attached file. Where is the error in file ?
Regards,
Prince
Regards,
Prince
You do not have the required permissions to view the files attached to this post.
Last edited by prince on 17 Jun 2016, 17:56, edited 1 time in total.
-
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How can i separate the record according to the Gender wi
The code uses the Advanced Filter feature of Excel. This feature requires that the field names at the top of the data ranges on the Boys and Girls sheets are EXACTLY the same as those on the All Record sheet.
On the All Record sheet, the field name in column E is "Name of Students", but the corresponding field name on the Boys and Girls sheets is "Student's Name". Since they are not the same, the code won't work.
You must either change the value of E5 on the All Record sheet to Student's Name, or change the value of D3 on the Boys and Girls sheets to Name of Students.
On the All Record sheet, the field name in column E is "Name of Students", but the corresponding field name on the Boys and Girls sheets is "Student's Name". Since they are not the same, the code won't work.
You must either change the value of E5 on the All Record sheet to Student's Name, or change the value of D3 on the Boys and Girls sheets to Name of Students.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: How can i separate the record according to the Gender wi
Thanks Mr. HansV , It's working now.
Thanks a lot Sir.
Regards,
Prince
Thanks a lot Sir.
Regards,
Prince
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: How can i separate the record according to the Gender wi
Hello Sir, In this attached file macro code of HideRow is functioning very well but why its showing the error message too. Kindly guide me where is the problem.
Thanks & regards
Prince
Thanks & regards
Prince
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: How can i separate the record according to the Gender wi
When the original code was developed we didn't realize you would be using formulas in the cells. The code was testing for empty cells, but since you use formulas, the cells, even though they return no value, are not empty. Adding a second condition into the formula should prevent the error from happening. Maybe there is a better way of testing this, but try the code below which should prevent the error now...
Code: Select all
Sub HideRows()
Dim shS As Worksheet
Dim shD As Worksheet
Dim rgS As Range
Dim rgD As Range
Dim rgC As Range
Set shS = Worksheets("Combined List")
Set shD = Worksheets("Sheet2")
Set rgS = shS.Range("A4", shS.Cells(Rows.Count, "A").End(xlUp)).Offset(0, 5)
Application.ScreenUpdating = False
shD.Rows.Hidden = False
For Each rgC In rgS.Cells
If Len(rgC.Offset(0, -2).Value) <> 0 Then
If rgC.Value = "" Then
Set rgD = shD.Cells.Find(What:=rgC.Offset(0, -2).Value, LookIn:=xlValues, LookAt:=xlPart)
If Not rgD Is Nothing Then
Range(rgD.Offset(-6), rgD.Offset(20)).EntireRow.Hidden = True
End If
End If
End If
Next rgC
Application.ScreenUpdating = True
End Sub
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: How can i separate the record according to the Gender wi
Mr. Rudi, Its not working.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: How can i separate the record according to the Gender wi
Sorry...my mistake.
I forgot to remove a statement rgS.Select in the macro above as I was testing it.
I removed it now in the code above, so reselect the above macro, copy it to your workbook and try it again.
I forgot to remove a statement rgS.Select in the macro above as I was testing it.
I removed it now in the code above, so reselect the above macro, copy it to your workbook and try it again.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How can i separate the record according to the Gender wi
And to be on the safe side, the line
should be
Code: Select all
Range(rgD.Offset(-6), rgD.Offset(20)).EntireRow.Hidden = True
Code: Select all
shD.Range(rgD.Offset(-6), rgD.Offset(20)).EntireRow.Hidden = True
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: How can i separate the record according to the Gender wi
TX Hans
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: How can i separate the record according to the Gender wi
Thank You Sir, its working.
Thanks a lot Sir.
Thanks a lot Sir.