fastest vba code to add (many) new rows into a protected table at the same time

[jimmalk]

Hello. I'm looking for a method to add multiple new rows into a protected table at the same time. I have this

Dim oList As ListObject                            ' list variable for the Reference Database
Dim RefTabHeader                                   ' 2-dimensional array for the database header

Private Sub UserForm_Initialize()
Set oList = ActiveCell.ListObject                  ' set oList to Reference Database
RefTabHeader = oList.HeaderRowRange.Value          ' populate 2-dimensional array with database header
End Sub

Private Sub FillInRows(bCopy As Boolean)
Dim oRow As ListRow
Dim i As Long
Application.ScreenUpdating = False
For i = 1 To CInt(Me.tbNumRows)
   Set oRow = oList.ListRows.Add(ActiveCell.Row - oList.Range.Row + 1)
Next i
End Sub

But I'd rather do it as a single row insert, rather than using the for-next loop. Looping takes too long if I insert many rows, because there's additional code that populates the inserted new rows. Is there a way to insert multiple new rows with only 1 line of code? Any suggestions please?

[nilem]

try something like this

Sub ert()
Dim i As Long: i = 5
With ActiveSheet.ListObjects(1)
    .Resize .Range.Resize(.ListRows.Count + i + 1)
End With
End Sub

[jimmalk]

I get the error "Cannot use this functionality on a protected sheet". I have in Workbook_Open()

Sheets("Sheet1").Protect UserInterfaceOnly:=True, AllowInsertingRows:=True, AllowDeletingRows:=True

shouldnt get this error then?

[nilem]

probably, there is only one way

Sub ert()
Dim i As Long: i = 5
With ActiveSheet
    .Unprotect
    With .ListObjects(1)
        .Resize .Range.Resize(.ListRows.Count + i + 1)
    End With
    .Protect UserInterfaceOnly:=True
End With
End Sub

AllowInsertingRows:=True allows you to insert rows into the worksheet, not in a ListObject

[jimmalk]

But wait Nilem!! I have an idea. Maybe... insert rows onto the sheet, instead of inserting directly onto the ListObject?? Is that possible? Because when I did the delete rows code, I believe I was deleting off the sheet, and not referencing the ListObject. What ya think?

[jimmalk]

Ahhh, I didnt know that! Thanks I've been able to delete rows from the table (and with the sheet protected) so I just assumed that inserting new rows should work too. I didnt know there was a difference. Thank you again Nilem

[nilem]

I also have an idea Suppose your table is in A1:D10. Run this macro on a protected sheet (with UserInterfaceOnly:=True)

ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(2, 1).Value = "123"

, and your table will automatically be raised.
Perhaps it will be suitable for you.

[jimmalk]

I got the rows to insert by unprotecting the sheet and running your earlier code, but the new rows inserted at the end of the table. I need to insert them after the ActiveCell. Therefore the above wont work either because it inserts at the end

[nilem]

... I need to insert them after the ActiveCell

for this case try

Sub ertExample()
Dim i As Long: i = 5
If Intersect(ActiveCell, ActiveSheet.ListObjects(1).Range) Is Nothing Then _
   MsgBox "active cell is outside the table", 64: Exit Sub
ActiveCell.Resize(i).EntireRow.Insert
End Sub

[jimmalk]

tbNumRows is the form text field containing the number of rows to insert. This did the trick

ActiveCell.Offset(1, 0).Resize(CLng(Me.tbNumRows)).EntireRow.Insert

It inserts empty rows down from the activecell (and the table is protected). Took only a few seconds of insert a 1000. Other method took about 10 minutes! Perfect!!! Thanks so much Nilem, great work. Un abrazo

[jimmalk]

I gave you a rep click on an earlier post & now it wont let me give you one on your last post. But I'll remember to get back later for your latest post too. Thanks