Hi,

I have a macro that currently states that if there is a value in column C and a value in column S, then enter a value in column D. The value in column C is just an increasing identifier that populates based on another macro. Column S has generic data keyed by a user. What I want to happen is for the value in column D to populate as 1 and only increase when the value in column S changes. For example:

Column C has an identifier in rows 8-12, and 16-20. If column S has "SHIRT" for rows 8-18 and "PANT" for rows 19-20. Column D should be "1" for rows 8-12, 16-18, and "2" for rows 19-20.

I have the macro almost there but I don't know how to account for the varying gaps in data. Basically, column D only needs to populate if Column C has a value, but it needs to reference column S to determine if the counter increases by 1 or not. Any help is greatly appreciated!!

Sub GroupSortOrder()
'
' GroupSortOrder Macro
'

'
Dim Cell As Range
    Dim Counter As Double
    Dim RegExp As Object
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Wks As Worksheet
    
        Set RegExp = CreateObject("VBScript.RegExp")
        RegExp.IgnoreCase = True
        
        RegExp.Pattern = "^(CWA|)\s"
        
        For Each Wks In Worksheets
        
            If RegExp.Test(Wks.Name) = True Then
        
                Set Rng = Wks.Range("C8:S8")
                Set RngEnd = Wks.Cells(Rows.Count, Rng.Offset(0, 16).Column).End(xlUp)
            
                If RngEnd.Row >= Rng.Row Then
            
                    Set Rng = Rng.Resize(RowSize:=RngEnd.Row - Rng.Row + 1)
                
                    For r = 1 To Rng.Rows.Count
                    If Rng.Item(r, 1) <> "" Then
                        If Rng.Item(r, 17) <> "" Then
                            
                                With Worksheets("DoNotEdit")
                                    Counter = .Cells(1, "D")
                                    Rng.Item(r, 2).Value = .Cells(1, "D")
                                    .Cells(1, "D").Value = Counter + 1
                                End With
                            End If
                    End If
                        
                    Next r
                
                End If
                With Worksheets("DoNotEdit")
                .Cells(1, "D").Value = 1
                End With
            End If
            
        Next Wks
        
        
End Sub