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
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
