Find bottom right cell, and then highlight bottom row

[ChrisAch]

Hi All.

Im trying to highlight a footer, without the data necessarily populated on the bottom row.

I know my column will always end with 'grand total' so thought find 'grand so tried the below ...


Cells.Find(What:="Grand", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

then offset 7,0

however what I would like to do, is from offset 7, highlight the whole row, worth of data, which is my footer, so that I can format.

Is there a way to find last row, in correlation to the last header, and select the whole row (just last row)

Thanks

[JKwan]

something like this

Sub test()
Dim WS As Worksheet

Set WS = ActiveSheet
lastrow = FindLastRow(WS, "A")
Rows(lastrow).Select
End Sub
Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function

[jolivanes]

All three of these should select the last row.

Sub Try_This()
Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column).EntireRow.Select
End Sub

Sub Or_This()
Cells(Cells.Find("*", , , , xlByRows, xlPrevious).Row, _
Cells.Find("*", , , , xlByColumns, xlPrevious).Column).EntireRow.Select
End Sub


Sub Or_Just_So()
Cells.Find("*", , , , xlByRows, xlPrevious).EntireRow.Select
End Sub

[SamT]

Assuming some part of the Footer is in Column "X"

Sub SamT()
Cells(Rows.Count, "X").End(xlUp).EntireRow.Select
End Sub

Assuming some part of the footer is always under the last header and the header is in Row 1

Sub SamT()
Cells(Rows.Count, Cells(1, Columns.Count).End(xlToLeft).Column).End(xlUp).EntireRow.Select
End Sub

[ChrisAch]

Hi

thanks for the replies.

very useful.

Not quite what I was looking for, and maybe I was not clear in my description ...

Rather than select the full 'last row' I only want to loop through, until the last cell that is parallel to row 1 last header data.

in this example, the last column will always be H:H

Thanks again

[SamT]

Sub SamT()
Cells(Rows.Count, "H").End(xlUp).Select
End Sub

[jolivanes]

You say "loop through". Loop through what? Remember, you know what your sheet looks like, we don't



Sub Who_Knows()
Cells(Rows.Count, 8).End(xlUp).Offset(, -7).Resize(, 8).Select
End Sub
If the Column becomes a variable (not Column H anymore)

Sub Who_Knows_B()
Dim lr As Long, lc As Long
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
Range(Cells(lr, 1), Cells(lr, lc)).Select
End Sub