VBA: Delete/Create rows from Table takes a long time

[inddon]

Hello There,

I have a Table and doing a process where it first deletes the rows and then based on the total rows of the new data to be inserted, create blank nth total rows in the same table.

In this case the total number of rows of data is 2774.

The below code deleted the rows except for the 1st row. This table has 22 columns having formulas. It takes approximate 60 seconds for both Delete and Create Blank rows in the table. The calculation mode is set to Manual in the start of the process.

..
Public wsFinal As Worksheet, tbl2 As ListObject

.....

'Code for Delete rows

Sub DeleteTableRows2()
    On Error Resume Next
    'Delete all table rows except first row from table2
    With tbl2.DataBodyRange
         If .Rows.Count > 1 Then
              .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
         End If
   End With
         
   'tbl2.DataBodyRange.RowHeight = 15
   wsFinal.Calculate
End Sub


'Code for Creating Blank rows


'CountClipboardRows is the total number of rows (eg. 2774)

Sub CreateRowsTable2()
    Set rng = Range("Table2[#All]").Resize(tbl2.Range.Rows.Count + CountClipboardRows, tbl2.Range.Columns.Count)
    tbl2.Resize rng
    'tbl2.Resize Range(Cells(1, 1), Cells(CountClipboardRows, tbl2.DataBodyRange.Columns.Count))
End Sub

There can be more than 10,000 rows.

Could you please advise how this can be made much faster?


Look forward to hearing from you.

Thanks & regards,
Don

 

[AlanSidman]

Have you tried adding at the beginning

Application.ScreenUpdating = False

and at the end

Application.ScreenUpdating = True

 

[inddon]

Hi Alan,

Thank you for your reply.

In the start I call SetApplicationOff and at the end SetApplicationOn

Sub SetApplicationOff()

 'Turn off events and screen updating
  With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
  End With
End Sub

Sub SetApplicationOn()
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
End Sub

I am a bit confused as to why it takes so much time. Also, I have removed all formatting as well.

Regards,
Don

 

[AlanSidman]

Without seeing the actual file, it is difficult to determine. Perhaps, if you uploaded a sample of your file, we may be able to determine the issue.

 

[inddon]

Without seeing the actual file, it is difficult to determine. Perhaps, if you uploaded a sample of your file, we may be able to determine the issue.

Hi Alan,

Thank you for your reply.

Please find attached the sample workbook for your reference.

I have created a worksheet 'User Manual', which explains how the process runs.

Look forward to hearing from you.

Regards,
Don

 

[Marc L]

Hi !

On my side your process takes also around 60s the first time
and around 80s the second time.
So if all that stuff is needed by code, there is nothing to upgrade …

But the question is all is really needed, what is the purpose of this process,
why a temporary worksheet ?

If you directly operate manually, does it take such a long time ?
​

 

[inddon]

Hi !

......
If you directly operate manually, does it take such a long time ?
​

Hi Marc,

Thank you for your reply and also for taking the time to test the workbook.

I am running a ERP report which gives me the periodical report in an Excel format. This data in this report needs to be reconciled. Therefore, this Excel workbook.

I open the Excel workbook generated by the ERP. Copy the data and paste it in the Sample Workbook. This was done manually.

Instead of me pasting the data manually in the Sample workbook I wanted VBA to handle it. Therefore, this program which would check if there are any contents in the clipboard, if yes
1. create a temp worksheet (just to count the number of rows)
2. paste the data in the temp worksheet and do the below
3. delete all rows (old data) from the tables.
4. create that many number of rows (count from temp worksheet) in the tables.
5. delete temp worksheet

Both the manual and the new process takes the same time.

Earlier last week, in the worksheet 'FinalData', I removed all the formulas and wrote it in VBA (attached workbook for your reference). After creating table rows, it would assign individually the column formulas. It didn't help took almost the same time.

This week I came to know about VBA 2 dimensional array and the users positive feedback that their calculation and process have improved ten fold. Thought if the worksheet 'FinalData' which has all the formulas could be written using arrays, would be helpful!. Just a thought. Would that help, what is your opinion?

Thanks again.

Look forward to hearing from you.

Regards,
Don

 

[Marc L]

Copy the data and paste it in the Sample Workbook.

Instead of me pasting the data manually in the Sample workbook I wanted VBA to handle it. Therefore, this program which would check if there are any contents in the clipboard, if yes
1. create a temp worksheet (just to count the number of rows)
2. paste the data in the temp worksheet and do the below
3. delete all rows (old data) from the tables.
4. create that many number of rows (count from temp worksheet) in the tables.
5. delete temp worksheet

Yes ! But why a temp worksheet ?!
What about clearing worksheet and directly paste data from source ?
​

 

[inddon]

Hi Marc,
Thank you for your reply.
Before the paste, I delete the old data rows and create new table rows based on the clipboard.
During this time the contents in the clipboard is lost ( i.e. ctrl + v is no longef available ).
That is the reason to hold it in a temp worksheet, then continue with delete/ recreate rows, copy from temp worksheet and then when job done delete the temp worksheet.
Hope this explains the reason.

Thanks again

Regards,
Don

 

[Marc L]

As a direct copy is faster, if you want a temp way,
nothing more to improve …
​

 

[inddon]

As a direct copy is faster, if you want a temp way,
nothing more to improve …
​

Thanks Marc, for your advise.

Regards,
Don