Locating specific rows in Macro

[KK Wong]

Hi there, I would like to create a macro to locate the number of specific rows. Please refer to the picture. From Column C, there are 2 sets of policy numbers. The first set is From C9 to C23. The second set is from C27 to C37. These policy numbers are fixed in Column C, but the number of policies are different (and therefore the range is different).

How can I code so that by running it, it can return 4 values (which represent the first and last rows of 2 sets of policy numbers).

In this case, the return values should be:
9
23
27
37

 

[Fluff]

Will the policies always begin in row 9?
Also what do you want to do with the row numbers?

 

[MARK858]

Are the blank cells in column C truly blank or are they formulas returning ""?
Am I right in assuming the data in column C are formulas?

 

[KK Wong]

Will the policies always begin in row 9?
Also what do you want to do with the row numbers?

The policy number should always begin in row 9 (but if it is possible, it would be better to identify the first policy number to be the one immediately below the cell value "Policy No."
The 4 return values will be used on another software (Uipath) as reference.

Are the blank cells in column C truly blank or are they formulas returning ""?
Am I right in assuming the data in column C are formulas?

Yes they are truly blank. The policy numbers are not the result of any formulas. The format of the policy numbers is Number; C8 is General. Others in Column C are blank cells.

 

[Fluff]

The 4 return values will be used on another software (Uipath) as reference.

Yes but what do you want the code to do with the row numbers?

 

[KK Wong]

Yes but what do you want the code to do with the row numbers?

Hi Fluff, do you mean what to do with the 4 return values? I plan to let the macro paste it somewhere in the worksheet, like E1:E4.

 

[MARK858]

Try the code below

Sub KKWong2()
    Dim area As Range, myFnd As Long, i As Long
    i = 1
    Application.ScreenUpdating = False
    
    myFnd = Columns("C").Find("Policy No.", , xlValues, , xlByRows, xlNext).Row + 1
    
    For Each area In Range(Cells(myFnd, "C"), Cells(Columns("C").Find("*", , xlValues, , xlByRows, xlPrevious).Row, "C")).SpecialCells(2, 23).Areas
        Cells(i, "E").Value = area.Cells(1).Row
        i = i + 1
        Cells(i, "E").Value = area.Cells(area.Cells.Count).Row
        i = i + 1
    Next
    
    Application.ScreenUpdating = True
End Sub

 

[KK Wong]

Try the code below

Sub KKWong2()
    Dim area As Range, myFnd As Long, i As Long
    i = 1
    Application.ScreenUpdating = False
   
    myFnd = Columns("C").Find("Policy No.", , xlValues, , xlByRows, xlNext).Row + 1
   
    For Each area In Range(Cells(myFnd, "C"), Cells(Columns("C").Find("*", , xlValues, , xlByRows, xlPrevious).Row, "C")).SpecialCells(2, 23).Areas
        Cells(i, "E").Value = area.Cells(1).Row
        i = i + 1
        Cells(i, "E").Value = area.Cells(area.Cells.Count).Row
        i = i + 1
    Next
   
    Application.ScreenUpdating = True
End Sub

Thank you for your solution!

 

[MARK858]

You're welcome