Excel VBA: Group on rows, Need to determine Start & End of each group

[mburch2000]

I have data in columns A through B where column A (RuleID) identifies rows that are considered together as a group.

I need to determine the Start Row and End Row for each Group (RuleID). This must be done with VBA code as it is part of a larger Macro.

Sample Data is:
RuleID Key
AB287 L
AB287 R
AB287 R
CN356 L
CN356 L
CN356 R
CN356 R
DC879 L
MX289 R
JJ546 L
JJ546 R
EH561 L

Code is Below:

Sub CaptureGrp()
'This Function determines the start & end of a RuleID Group
Dim CurrRuleID As String, PrevRuleID As String, NextRuleID As String
Dim SameGroup As Boolean, NewGroup As Boolean
Dim rowStart As Integer, rowEnd As Integer, lRow As Integer
    
lRow = 5   'Data starts on line 5
rowStart = 0
rowEnd = 0
       
For lRow = lRow To 16
    CurrRuleID = Cells(lRow, "A").Value
    PrevRuleID = Cells(lRow - 1, "A").Value
    NextRuleID = Cells(lRow + 1, "A").Value
        
    If CurrRuleID = PrevRuleID Then
        SameGroup = True
        Worksheets("Source").Cells(lRow, 6).Value = SameGroup
        
        If CurrRuleID <> NextRuleID Then
            rowEnd = lRow
        ElseIf CurrRuleID = NextRuleID Then
            rowStart = lRow - 1
        End If
     
     ElseIf CurrRuleID <> PrevRuleID Then
        NewGroup = True
        Worksheets("Source").Cells(lRow, 7).Value = NewGroup
        
        If rowEnd = 0 Then
            rowStart = lRow
        End If
    End If
    Worksheets("Source").Cells(lRow, 4).Value = rowStart
    Worksheets("Source").Cells(lRow, 5).Value = rowEnd
        
'If rowStart & rowEnd have been assigned, then reset them
    If rowStart <> 0 And rowEnd <> 0 Then
        rowStart = 0
        rowEnd = 0
    End If
Next lRow
End Sub

Thanks for the help. I appreciate it!

Mike

[NeoPa]

While this question is sufficiently different from your other one that I won't delete it, there is neveretheless a post in the other thread that obviates this question.

It uses the Range.Find() function.

[mburch2000]

Thanks for your help. I just got it to work before reading this post. I will research the above function. It may be an improvement on what I got, but thank you for hanging in there with me. I really appreciated it!

Thanks,
Mike

[NeoPa]

No worries Mike.

I expect the code in the other thread will help to put it into perspective.