Select last used Row in Table

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,427
Office Version
365, 2010
Platform
Windows, Mobile
Maybe...

VBA Code:
j = Range(loTable).Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row
Rows(j).AutoFit
Application.Goto Cells(j + 1, Range(loTable).Columns(1).Column)
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
I've figured it out now, using

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & LastRow).Select
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,427
Office Version
365, 2010
Platform
Windows, Mobile
I've figured it out now, using

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & LastRow).Select
Put any text in a cell in column A below the table. Do you still get the result that you want?
 

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
Hi MARK828

You are of course correct, it doesn't work.

I'm not sure I understand enough though, hence I'm getting the following message when running the code,

Method 'Range'of object'_Global' failed.

???
 

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
The following line is highlighted.

VBA Code:
j = Range(loTable).Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,427
Office Version
365, 2010
Platform
Windows, Mobile
The only way that would come up is if it isn't seeing the range. It works fine for me.
See the workbook in the link below.
The only thing that I have done is qualify the ranges for extra cover in case you run it from another sheet and commented out the sheet protection.
There is a button on Sheet1 that runs the macro.

 

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
This is now showing the following error,

Runtime Error 91: Object variable or with block variable not set

I've checked other posts etc. but can't figure out why.
 

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
On the same line,

VBA Code:
j = oSheetName.Range(loTable).Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,427
Office Version
365, 2010
Platform
Windows, Mobile
If you are running the code on the workbook that I posted then I can't answer that as the workbook that I posted works on my computer.
If you are running the code in the workbook on your original workbook are you sure that you turned the protection off on the sheet if you are running the code as I stated
The only thing that I have done is qualify the ranges for extra cover in case you run it from another sheet and commented out the sheet protection.
or have you declared j as a range rather than a long?
 

Watch MrExcel Video

Forum statistics

Threads
1,096,184
Messages
5,448,847
Members
405,533
Latest member
Heretical1

This Week's Hot Topics

Top