Select last used Row in Table

[AndyEd]

I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which require the Row to expand to fit.

Due to everything being locked down the user cannot manually adjust the Row height or via the Autofit function. A VBA enabled button allows the user to create new Rows as required.

I've managed to incorporate code to Autofit the Row height, as long as the user stays within the last Row prior to hitting the button. If they tab across fine, however if they hit <Enter> and move down a Row (out of the Table and into the sheet) or click anywhere else, the new Row is adjusted only. All the functionality is then locked out, as is necessary.

Can someone advise how I can ensure a cell in the last Row containing information is selected?

Thanks,

 

[MARK858]

Maybe...

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

 

[AndyEd]

I've figured it out now, using

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

 

[MARK858]

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]

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.

???

 

[MARK858]

Method 'Range'of object'_Global' failed.

With which code? and on what line?

 

[AndyEd]

The following line is highlighted.

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

 

[MARK858]

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.xlsm | Powered by Box

app.box.com

 

[AndyEd]

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]

On the same line,

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

 

[MARK858]

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?