Return Row Number Based on Last Empty Cell in Different Column/Specified Range?

[soybomb10]

Hello all,

Above is basically what my spreadsheet looks like but with a little more detail, but this is all I think I need to provide for a gist of what I am having a problem with.

Basically what I am trying to do is the following:
- For each ID number, search column C (status column) for the last cell with anything in it (within the range of the ID number, not the entire column) and return that row number
- Then return the step number of one row after the row found in previous step
- Repeat process for next ID number

Hopefully I've explained this well and this can be done through VBA...thanks for the help!

 

[soybomb10]

To give an example of what the desired output/process would be with above image:

Macro would identify the range of cells in column C where column A is ID = 1
Then it would search column C within that range and return row = 4 because that is the last nonempty row/cell
Then it would go to column B, add one to the value found in previous step (so row 5 now), and return the step number in row 5 which is 4

 

[Osvaldo Palmeiro]

Hi.
See if this code could help you.
The "Step Number" founded will be on column 'D'.

Sub FindStep()
 Dim lngStatusRow As Long, k As Long, x As Long
  For k = 2 To Cells(Rows.Count, 1).End(3).Row
   x = Application.CountIf(Range("A2:A" & Cells(Rows.Count, 1).End(3).Row), Cells(k, 1))
    If Cells(k + x - 1, 3) <> "" Then
     lngStatusRow = k + x - 1
    Else: lngStatusRow = Cells(k + x - 1, 3).End(3).Row
    End If
    Cells(k + x - 1, 4) = Cells(lngStatusRow + 1, 2)
    k = k + x - 1
  Next k
End Sub