Hi,

This returns 2 variables. Highrow - the row above the active cell and LowRow.

Right click your sheet tab, view code and paste the code below in. I didn't
trap for not find the text and you may want to do this

Sub High_Low()
Dim HighRow As Long, LowRow As Long
Dim LastRow As Long
For x = ActiveCell.Row To 1 Step -1
If UCase(Cells(x, 1).Value) = "TOTALS" Then
HighRow = Rows(x).Row
Exit For
End If
Next

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For x = ActiveCell.Row To LastRow
If UCase(Cells(x, 1).Value) = "TOTALS" Then
LowRow = Rows(x).Row
Exit For
End If
Next
End Sub

Mike

"John" wrote:

Column A has the text "Totals" in several random cells. In VBA I would like
2 variables that return the row numbers of the first occurances of "Totals"
in Column A of the rows above and below the active cell, regardless of where
the active cell is. Thank you.