Select last used Row in Table

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
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?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
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.
 

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
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...
 

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
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??
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
Sorry but I won't download from that site as there is no control on the cookies that they place on my computer.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
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:

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
Thought it might be something I'd overlooked. Thank you.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,404
Office Version
365, 2010
Platform
Windows, Mobile
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,095,727
Messages
5,446,168
Members
405,388
Latest member
Arlind

This Week's Hot Topics

Top