Set a dynamic range based on common values in a column

Question

I want to give a "Timepoint" value (which I call a scanRank in the code below) to all the rows in this table based on "Subject ID".

In this example, the first subject is already completed.

Subject ID	Group Info	Scan date	Timepoint
1	group 1	20250225	1
1	group 1	20250305	2
1	group 1	20250320	3
1	group 1	20250404	4
1	group 1	20250404	4
1	group 1	20250404	4
1	group 1	20250404	4
3	group 2	20250225
4	group 3	20250225
4	group 3	20250305
4	group 3	20250321
4	group 3	20250321
4	group 3	20250407
4	group 3	20250407
4	group 3	20250407
4	group 3	20250407
5	group 3	20250227
5	group 3	20250227
5	group 3	20250306

I want VBA to recognize the first row and the last row for each common Subject ID, to stop the range when the Subject ID changes.
Once the macro has cell addresses for the start and end, then it can run the for loop to get the "timepoints" for each Subject ID then move on to the next Subject ID.

In the code below, I don't know how to code the "startSubID", "endSubID", "scanRow", or "prevscanRow" variables. I was thinking of a Do loop.

Private Sub CommandButton1_Click()

Dim ws4 As Worksheet
Set ws4 = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
  
Dim ScanDates As Range, SingScanDate As Range
Dim SubIDs As Range, UniqueSubID As Range

lastrow = ws4.Cells(ws4.Rows.Count, 3).End(xlUp).Row
Set SubIDs = ws4.Range("C12:C" & lastrow)


For Each UniqueSubID In SubIDs
 '   startSubID = ??? starting row based on Subject ID in Column C
 '   endSubID = ??? ending row based on Subject ID in Column C
 
Set ScanDates = ws4.Range(ws4.Cells(startSubID, 6), ws4.Cells(endSubID, 6))

scanRank = 1
scanRow = startSubID.Address
prevscanRow = startSubID.Address.Offset(1, 0)

For Each SingScanDate In ScanDates
    ws4.Cells(scanRow, 6).Value = scanRank
    If ws4.Cells(scanRow, 5).Value = ws4.Cells(prevscanRow, 5).Value Then
    ws4.Cells(scanRow, 6).Value = ws4.Cells(prevscanRow, 6).Value
    End If
    scanRow = scanRow + 1
    prevscanRow = prevscanRow + 1
    scanRank = scanRank + 1
    
Next SingScanDate
  UniqueSubID = endSubID + 1
  Next UniqueSubID

End Sub

Yes, I always will sort it first based on Subject ID and Scan Date! — Rae Van Sandt
– Rae Van Sandt, Commented Aug 19 at 23:03

Tim Williams · Accepted Answer · 2025-08-20 04:21:13Z

2

Something like this maybe:

Option Explicit

Private Sub CommandButton1_Click()

    Dim ws4 As Worksheet, c As Range, subjId As String, currSubjId As String
    Dim dt As Date, currDt As Date, scanRank As Long
    
    Set ws4 = ThisWorkbook.Sheets("Sheet1")
    currSubjId = vbTab 'not a valid id...
    
    For Each c In ws4.Range("C12:C" & ws4.Cells(Rows.Count, "C").End(xlUp).Row).Cells
        subjId = c.Value                      'subject id
        dt = c.EntireRow.Columns("E").Value   'scan date
        
        If subjId <> currSubjId Then     'new subject?
            scanRank = 1
            currDt = dt
            currSubjId = subjId
        ElseIf dt <> currDt Then         'same subject but new date?
            scanRank = scanRank + 1      'increment the counter
            currDt = dt
        End If
        
        c.EntireRow.Columns("F").Value = scanRank  'assign the counter to this row
    
    Next c
    
End Sub

Formula version (in F12 then fill down):

=IF(COUNTIF(C$12:C12,C12)=1,1,IF(E12<>E11,F11+1,F11))

edited Aug 20 at 4:21

answered Aug 19 at 23:12

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rae Van Sandt Aug 19 at 23:27

Thank you. It looks like it will work, but I got to the "dt" variable and it says type mismatch. Does it have to declared as a date? Because it is a date, but in that format it also is a number that is bigger if the date is later in time....right?

Rae Van Sandt Aug 19 at 23:29

Ahh! Yes, I declared dt and currDt as long and it worked! Thank you thank you!

Tim Williams Aug 19 at 23:30

OK - good to hear you figured it out.

Collectives™ on Stack Overflow

Set a dynamic range based on common values in a column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related