Excel VBA Find last row number where column “C” co

Question

Seeking a method in Excel VBA to Find last row number where column "C" contains a known value.

Answer 1

This will find the last occurrence of happiness in column C

Sub SeekHappiness()
    Dim C As Range, where As Range, whatt As String
    whatt = "happiness"
    Set C = Range("C:C")
    Set where = C.Find(what:=whatt, after:=C(1), searchdirection:=xlPrevious)
    MsgBox where.Address(0, 0)
End Sub

To output the row number only, use:

MsgBox Mid(where.Address(0, 0), 2)

To find the first occurrence:

Sub SeekHappiness()
    Dim C As Range, where As Range, whatt As String
    whatt = "happiness"
    Set C = Range("C:C")
    Set where = C.Find(what:=whatt, after:=C(1))
    MsgBox where.Address(0, 0)
End Sub

Answer 2

You could loop through the column to find the last occurrence of a value.

Sub findLastRow()
  Dim searchValue As String
  Dim endRow As Integer
  Dim lastRowSearchValue As Integer

  searchValue = "testValue"        ''enter your search value
  With Worksheets("sheet1")        ''enter the name of your worksheet
      endRow = .Cells(Rows.Count, 3).End(xlUp).Row   
      For i = 1 To endRow
          If .Cells(i, 3) = searchValue Then
              lastRowSearchValue = i
          End If
      Next i
  End With
End Sub

Just replace the value of the variable "searchValue" with whatever is the value you're looking for (maybe change the type of the variable if its not a string) and the Sub will store the index of the last row of the occurrence of the searchValue in the variable "lastRowSearchValue" for further use.