JILBO
09-10-2021, 03:04 AM
Hi,
I have some VBA i utilise to Add 10 Rows to the bottom of a table [ChgTBL].
The Table has an Index counter currently using a =ROW() function, however when through another method rows are deleted it gives a 'REF! error in the index column.
Is there another method that could be employed which would populate the Index and countinue numbering based and the last cells value +1?
Thanks
Public Sub AddRow()
'
' Code for ADD RECORD Button in Ribbon
'Unprotect Sheet so new Row can be added
ActiveSheet.Unprotect
Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Application.ScreenUpdating = False
Set tbl = ws.ListObjects("ChgTBL")
Dim i As Byte
'Adds 10 rows to end of Table
For i = 1 To 10
'add a row at the end of the table
tbl.ListRows.Add AlwaysInsert:=True
Next i
' Range("ChgTBL[#All]").Select
' Selection.RowHeight = 31
'Protect Sheet after adding rows, Set the access of the locked cells if required.
'ActiveSheet.Protect
Range("ChgTBL[Serial]").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
True, AllowFiltering:=True, AllowDeletingRows:=True, AllowSorting:=True _
, AllowUsingPivotTables:=True, AllowFormattingCells:=True
Application.ScreenUpdating = True
End Sub
I have some VBA i utilise to Add 10 Rows to the bottom of a table [ChgTBL].
The Table has an Index counter currently using a =ROW() function, however when through another method rows are deleted it gives a 'REF! error in the index column.
Is there another method that could be employed which would populate the Index and countinue numbering based and the last cells value +1?
Thanks
Public Sub AddRow()
'
' Code for ADD RECORD Button in Ribbon
'Unprotect Sheet so new Row can be added
ActiveSheet.Unprotect
Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Application.ScreenUpdating = False
Set tbl = ws.ListObjects("ChgTBL")
Dim i As Byte
'Adds 10 rows to end of Table
For i = 1 To 10
'add a row at the end of the table
tbl.ListRows.Add AlwaysInsert:=True
Next i
' Range("ChgTBL[#All]").Select
' Selection.RowHeight = 31
'Protect Sheet after adding rows, Set the access of the locked cells if required.
'ActiveSheet.Protect
Range("ChgTBL[Serial]").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
True, AllowFiltering:=True, AllowDeletingRows:=True, AllowSorting:=True _
, AllowUsingPivotTables:=True, AllowFormattingCells:=True
Application.ScreenUpdating = True
End Sub