CODEWITHSUNDEEP
vbaexcel
Goos van den Bekerom
Goos van den Bekerom

Reputation: 1493

Is there a faster code for adding a new row to a table - Excel VBA

I add a new row to a table, everytime my code finds a new value to add to it.
The code I use for this Set newRow = ProjectTable.ListRows.Add works fine.
but this makes it run very slow.

Is there a code that accomplishes the same but runs quicker?

Dim ProjectName As String
Dim ResourceType As String
Dim newRow As ListRow
Dim RPLastRow As Long
RPLastRow = RPSheet.Cells(Rows.Count, 1).End(xlUp).Row

For Each cell In RPSheet.Range("A5:A" & RPLastRow)
    If cell = project Then
        Dim cRow As Long
        cRow = cell.Row

        'enter resource type to table
        ResourceType = RPSheet.Range("B" & cRow).Value
        Set newRow = ProjectTable.ListRows.Add
        newRow.Range(1, 1).Value = ResourceType

        'find amount of resources linked to project and add number to table
        ProjectName = project
        newRow.Range(1, 2).Value = Sheet2.NumberOfResources(ProjectName, ResourceType)
    End If
Next cell

EDIT: Added some extra code so its more clear

Upvotes: 4

Views: 4408

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

The faster way is to add data to the end of the table and then simply resize it.

Here is an example

ProjectTable.Resize Range("$A$1:$E$" & lRow)

Where lRow is the new last row

Upvotes: 3

Related Questions