Select last used Row in Table

AndyEd

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

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

sparky2205

Board Regular
Joined
Feb 6, 2013
Messages
231
Office Version
365, 2016
Platform
Windows
Can you not just allow formatting of rows when protecting?
 

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
I've tried that but it doesn't work.
 

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
I've simply used

Selection.Rows.Autofit

Once the protection is removed. The protection is then reinstated.
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,038
Please show the entire sub that uses this, for instance if this is the SelectionChange event then show us that event's code.
 

sparky2205

Board Regular
Joined
Feb 6, 2013
Messages
231
Office Version
365, 2016
Platform
Windows
I've simply used

Selection.Rows.Autofit

Once the protection is removed. The protection is then reinstated.
When you reinstate the protection are you allowing formatting of rows?
.Protect Password = "MyPass", AllowFormattingRows:=True

This is why we need to see your code.
 

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
Sorry for the delay, the code is as follows,

VBA Code:
 'VBA Add New Row to Table
Sub Add_Row_to_Table()

    ' Unprotect the Worksheet
    ActiveSheet.Unprotect Password:="password"
    
    ' Adjust the current Row to fit entries
    Selection.Rows.AutoFit
        
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    
    'Define Variable
    sTableName = "Table1"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Audit table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    'Add New row to the table
    loTable.ListRows.Add
    
    ' Protect the Worksheet
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
sparky2205, are you suggesting that even when I allow Row formatting before protecting the sheet by not coding for it as you've suggested that it's turning the formatting 'off'?
 

AndyEd

New Member
Joined
May 13, 2020
Messages
16
Office Version
2016
Platform
Windows
ok I see that by including
Code:
AllowFormattingRows:=True
it keeps the function 'live' so that the user can return to the Row and use the Autofit Row Height, however it still means that they have to 1) Select the Row and 2) Select Autofit Row Height.

What I'd like to happen is that regardless of which cell is selected, on pressing the 'New Row' button a cell in the last used Row will be selected, the Autofit function is fired and the cursor then moves to the new Row for data entry.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,187
Messages
5,448,860
Members
405,535
Latest member
KLFT

This Week's Hot Topics

Top