Copying and Pasting a table and then Deleting Rows | Excel forum

Resolved · Urgent Priority · Version 2007

Fri 23 Nov 2012, 14:34 Delegate Sam said...

Sam has attended:
Excel VBA Intro Intermediate course

Copying and Pasting a table and then Deleting Rows

Hi again,

I would like to copy and past a table and then delete empty rows. I've written two bits of code that work, one is a loop with a do while loop that deletes a row as long as it is empty and then repeats the loop. The other is an if statement and if the row is empty it deletes it and then sets i to 1-1 and loops. Both take a very long time- is there a quicker way of doing this?

Thanks,
Sam

Mon 26 Nov 2012, 10:51 Trainer Gary said...

RE: Copying and Pasting a table and then Deleting Rows

Hi Sam

Thanks for getting in touch.

Does your code 'select' the rows while it deletes? This can slow things right down. You could try something like:

dim r As Range, rows As Long, i As Long
Set rng = ActiveSheet.Range("A1:J50")
rows = rng.rows.Count
For i = rows To 1 Step (-1)
If WorksheetFunction.CountA(rng.rows(i)) = 0 Then rng.rows(i).Delete
Next i

This FOR NEXT loop loops backwards through the range A1:J50, and if a COUNTA function returns zero, delete the row (you go backwards as deleting an entire row causes the selection to shuffle 'up').

A really neat way to improve performance is to remove some of the automation services:

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Disables formula calculation and screen updates. You should insert this at the top of your code, with the corresponding code at the bottom:

With Application
.Calculation = Automatic
.ScreenUpdating = True
End With

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector