Count Rows Until Cell Value or Cell Value Changes

[STIguy]

Experts,

I'm stuck on a custom sort. I have converted the data into a tiered format, but now I would like to go through the "Level" column and determine the number of Level 3 entries in each group. For the following data I would like to return the number of rows in each Level 3 group. A named integer would be perfect.

L2item Group 1 - 5 rows of Level 3 items
L2item Group 2 - 4 rows of Level 3 items
L2item Group 3 - 3 rows of Level 3 items

Any advice is greatly appreciated!

Data set:

[TABLE="width: 321"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Level[/TD]
[TD]L2 Item[/TD]
[TD]PN[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]1000[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]501[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]502[/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]503[/TD]
[TD]Snake[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]504[/TD]
[TD]Shark[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]506[/TD]
[TD]Tiger[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]601[/TD]
[TD]Spider[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]602[/TD]
[TD]Zebra[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]103[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]701[/TD]
[TD]Fish[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]703[/TD]
[TD]Pig[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]704[/TD]
[TD]Cougar[/TD]
[/TR]
</tbody>[/TABLE]

 

[Pecan]

I was able to accomplish this by applying Subtotals (Data Tab, Outline Section, Subtotal) to your information two times. You must ensure that you check the box to not replace existing subtotals. Here's what I was able to get. [TABLE="width: 360"]
<tbody>[TR]
[TD][/TD]
[TD]Level[/TD]
[TD]L2 Item[/TD]
[TD]PN[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1000[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]1 Count[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]2 Count[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]501[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]502[/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]503[/TD]
[TD]Snake[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]504[/TD]
[TD]Shark[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]506[/TD]
[TD]Tiger[/TD]
[/TR]
[TR]
[TD]3 Count[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[TD]1 Count[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]2 Count[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]601[/TD]
[TD]Spider[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]602[/TD]
[TD]Zebra[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3 Count[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]2 Count[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]103[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]2 Count[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]701[/TD]
[TD]Fish[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]703[/TD]
[TD]Pig[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]704[/TD]
[TD]Cougar[/TD]
[/TR]
[TR]
[TD]3 Count[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]3 Count[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16[/TD]
[TD]Grand Count[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="5"></colgroup>[/TABLE]

 

[mumps]

Try this macro. I am assuming that your data is located to columns A to D. Because cell B2 is blank, the first count will result in a zero.

Sub CountRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("B1:B" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("B1:B" & LastRow), Unique:=True
    Set rngUniques = Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible)
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    Dim fRow As Long
    Dim lRow As Long
    Dim strFirst As Range, strLast As Range
    Dim rng As Range
    Dim counter As Long
    For Each rng In rngUniques
        Set strFirst = Range("B2:B" & LastRow).Find(What:=rng, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        If Not strFirst Is Nothing Then
            fRow = strFirst.Row
            Set strLast = Range("B2:B" & LastRow).Find(What:=strFirst, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False)
            If Not strLast Is Nothing Then
                lRow = strLast.Row
            End If
        End If
        counter = Application.WorksheetFunction.CountIf(Range(Cells(fRow, "A"), Cells(lRow, "A")), "3")
        MsgBox ("L2Item Group " & rng & " - " & counter & " rows of Level 3 Items")
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[STIguy]

Pecan,

Thanks for the quick reply, but I'm looking to do this in VBA because I would like to apply some code once I obtain the row counts. I was thinking of a format something like the code below. I'll need to think of a way to skip to the next range of 3 because the way I have it written currently it will loop through each row.

Sub SortAssemblyTable()

    Application.ScreenUpdating = False
    
'Range will be variable later
    Dim rng As Range
        Set rng = Range("B6:M25")
    
    Dim rw As Range
    Dim cll As Range
    Dim Level3Count As Long
    
    For Each rw In rng.Rows
        For Each cll In rw.Columns


            If cll.Column = rng.Columns(1).Column And cll.Value = 3 Then
               
                Level3Count = 1
                Do Until cll.Column = rng.Columns(1).Column And cll.Value = 2
                    
'Enter some code here for rows.count or countif?


                Loop
                
                'Apply other code based on number of level 3 rows.
                
            End If


        Next cll
    Next rw
End Sub

 

[STIguy]

mumps - just saw your post. This is great! I need to figure out how to incorporate it into the existing code i've got. Thanks!

 

[mumps]

My pleasure.

 

[STIguy]

mumps - could you explain this section of code:

Range("B1:B" & lastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _        ("B1:B" & lastRow), Unique:=True
    Set rngUniques = Range("B2:B" & lastRow).SpecialCells(xlCellTypeVisible)
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

So from my understanding you are filtering the level 2 items to determine how many ranges there based on unique values. However it looks like you are essentially defining three different rngUniques to be analyzed in the FOR loop. How is this possible???

 

[mumps]

That section of code looks in column B and creates a range composed of the unique values, in this case 1, 2 and 3. The rest of the code finds the first and last rows of each group of unique values and uses those rows to count the 3's in column A in the corresponding range. Does this make sense?

 

[STIguy]

Yea, that makes sense. I'm trying to understand where this range is stored because isn't filtering in place typically destructive?

 

[mumps]

The list of unique values is stored in the variable rngUniques. Once the variable is created, any changes made to the sheet don't affect the contents of the variable.