PDA

View Full Version : Trouble with Grouping



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.

Bob Phillips
07-18-2012, 01:39 AM
Where is the Find_Range code?

av89
07-18-2012, 06:44 AM
Here it is. I don't know if I can change it to look for ranges of numbers.
Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlPart, _
Optional MatchCase As Boolean = False) As Range

Dim c As Range, FirstAddress As String

With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _
SearchFormat:=False) 'Delete this term for XL2000 and earlier
If Not c Is Nothing Then
Set Find_Range = c
FirstAddress = c.Address
Do
Set Find_Range = Union(Find_Range, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

End Function
I'm not constrained to using only this function. If there is a different approach entirely I'd love to hear about it. I'm fairly new so I don't always know the best approach to a problem.