av89
07-17-2012, 12:16 PM
Hey guys,
I'm having trouble getting a spreadsheet to do exactly what I want it to do. I've attached an example worksheet for easier understanding. I need to group rows based on certain criteria. I would like all of the rows with a blank in the 'Note' (e.g. column B) column to group themselves and be counted and I also want a second group that is composed of the rows with blanks in the 'Code' (e.g. column C) column to be grouped and counted. This would all need to be displayed on a separate sheet from the master. It is possible for one row to qualifty for both groupings in which case it needs to present in both.
I'm using a find_range function to find the instances where each case is true.
'find all rows where "Note" is blank and copies to cover
Sheets("Master").Activate
Find_Range("", Columns("B"), xlValues, xlWhole).EntireRow.Copy Range("Cover!A65536").End(xlUp).Offset(1, 0).EntireRow
Sheets("Cover").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'groups each row that has a blank in that column and hides grouping.
For i = 2 To LastRow
If Not Cells(i, 2) <> "" Then
Cells(i, 2).EntireRow.Group
End If
Next i
ActiveSheet.Outline.ShowLevels RowLevels:=1
It works when I'm only looking at the 'note' column but when i try to run this code twice to find and group the blanks in the 'code' column it breaks.
Any help would be appreciated. Thanks.
I'm having trouble getting a spreadsheet to do exactly what I want it to do. I've attached an example worksheet for easier understanding. I need to group rows based on certain criteria. I would like all of the rows with a blank in the 'Note' (e.g. column B) column to group themselves and be counted and I also want a second group that is composed of the rows with blanks in the 'Code' (e.g. column C) column to be grouped and counted. This would all need to be displayed on a separate sheet from the master. It is possible for one row to qualifty for both groupings in which case it needs to present in both.
I'm using a find_range function to find the instances where each case is true.
'find all rows where "Note" is blank and copies to cover
Sheets("Master").Activate
Find_Range("", Columns("B"), xlValues, xlWhole).EntireRow.Copy Range("Cover!A65536").End(xlUp).Offset(1, 0).EntireRow
Sheets("Cover").Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'groups each row that has a blank in that column and hides grouping.
For i = 2 To LastRow
If Not Cells(i, 2) <> "" Then
Cells(i, 2).EntireRow.Group
End If
Next i
ActiveSheet.Outline.ShowLevels RowLevels:=1
It works when I'm only looking at the 'note' column but when i try to run this code twice to find and group the blanks in the 'code' column it breaks.
Any help would be appreciated. Thanks.