I'd like to name a range and go to the last row with data in that range.

"David Godinger" wrote in message
I'd like to name a range and go to the last row with data in that range.

one way -

Dim rng As Range, rngBR As Range

Set rng = ActiveSheet.Range("B2:Z100")
With rng
Set rngBR = .Cells(.Rows.Count, .Columns.Count)
End With
'Debug.Print rngBR.Address
Application.Goto rngBR

Peter T

The macro took me to the last cell (the lower right of the entire range). By the way, the lower half of the range had no text or numbers in it.

However, there was formatting in all cells of the range for color, number, and border. Could it be that the macro considers the formatting as data? If so, would it be possible for the macro to work only on numbers, dates, and text?

Thanks!

Dave G.

"David Godinger" wrote in message
The macro took me to the last cell (the lower right of the entire range).
By the way, the lower half of the range had no text or numbers in it.

However, there was formatting in all cells of the range for color, number,
and border. Could it be that the macro considers the formatting as data?
If so, would it be possible for the macro to work only on numbers, dates,
and text?

Sorry I misread you wanted the last row 'with data'.

It would simplify things if you wanting the last cell in a specified column
(rather than all columns that includ data, for example the in the first
column of the given range - If that's that an option try this

last data cell in first column of the range

lastRow = rng.Offset(rng.Rows.Count, 1 - 1).Cells(1).End(xlUp).Row
' lastRow = actual row

' or last row with data in col-1 of the range
lastRow = lastRow - rng.Rows(1).Row + 1
Debug.Print rng(lastRow, 1).Address

Peter T

Yes, getting the last cell of text or numbers in one column is easy, but that's not what I'm asking for.

Again, I've appreciated your thoughts, thanks.


On Sunday, October 25, 2020 at 11:11:56 AM UTC-7, Peter T wrote:
"David Godinger" wrote in message
The macro took me to the last cell (the lower right of the entire range).
By the way, the lower half of the range had no text or numbers in it.

However, there was formatting in all cells of the range for color, number,
and border. Could it be that the macro considers the formatting as data?
If so, would it be possible for the macro to work only on numbers, dates,
and text?
Sorry I misread you wanted the last row 'with data'.

It would simplify things if you wanting the last cell in a specified column
(rather than all columns that includ data, for example the in the first
column of the given range - If that's that an option try this

last data cell in first column of the range

lastRow = rng.Offset(rng.Rows.Count, 1 - 1).Cells(1).End(xlUp).Row
' lastRow = actual row

' or last row with data in col-1 of the range
lastRow = lastRow - rng.Rows(1).Row + 1
Debug.Print rng(lastRow, 1).Address

Peter T

"David Godinger" wrote in message

Yes, getting the last cell of text or numbers in one column is easy, but
that's not what I'm asking for.

OK try this -

Sub test()
Dim rngData As Range
Dim rngFound As Range

Set rngData = Range("B2:J20")

Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows,
xlPrevious)

If rngFound Is Nothing Then
MsgBox rngData.address & " is empty"
Else
MsgBox rngFound.Address
End If

End Sub

Peter T