Select last used Row in Table

AndyEd

Board Regular
Joined
May 13, 2020
Messages
124
Office Version
  1. 365
Platform
  1. Windows
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,
 
Your works but of course there is no protection to any cell can be altered. I've tried it on my workbook but I get the Error 91 message? Can't figure out why.

The only thing with yours is the lack of protection, for instance if the column 'Product Name' was a Lookup from a preceding column, i.e. 'Product Code', then how could you ensure that the person keying wouldn't inadvertently overtype the Lookup formula and therefore lose the product details as they progressed through completing the fields in the table?
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I just turned off the protection as I didn't want my sheet having protection at the end, it makes no difference to the code running correctly as long as your unprotect line does actually unprotect the correct sheet.
 
Upvote 0
I've copied your code into a sanitised copy of my Workbook. I'm still getting the same error message.

I obviously can't attach it. I'm sure it's going to be something trivial...
 
Upvote 0
Here's the link

Audit - Copy.xltm

You will note that Columns 1, 3 & 7 all contain formula which I'd want to copy to each new Row as it's created.

The cells in Columns 8 & 10 will contain freetext entries which will be separated using <alt+enter> to that the text is not wrapped. These cells will more than likely need to be expanded, hence the Row height changing, which I'd like to happen when the "New Row" button is pressed, and then select the cell corresponding to Column2 in the new Row.

The only way I can protect the formula in Columns 1, 3 & 7 is to protect them and the Worksheet??
 
Upvote 0
Sorry but I won't download from that site as there is no control on the cookies that they place on my computer.
 
Upvote 0
It is because your table is empty....

VBA Code:
Dim j As Range
    Set j = oSheetName.Range(loTable).Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious)
    If Not j Is Nothing Then
        oSheetName.Rows(j.Row).AutoFit
        Application.Goto oSheetName.Cells(j.Row + 1, oSheetName.Range(loTable).Columns(1).Column)
    Else
        oSheetName.Rows(loTable.DataBodyRange.Cells(1).Row).AutoFit
        Application.Goto oSheetName.Cells(loTable.DataBodyRange.Cells(1).Row, oSheetName.Range(loTable).Columns(1).Column)
    End If
 
Last edited:
Upvote 0
Thought it might be something I'd overlooked. Thank you.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top