Most Efficient Way to Delete Blank Rows?

[Tuta]

All,

I have a macro that currently selects blank cells and deletes the entire row. This file is around 9,000 rows of data in which 1,800 or so are being deleted.

Range("A1").Select
ActiveCell.Offset(2, 2).Range("A1:A" & FinalRow - 1).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

It is currently bogging down Excel 2010/64bit on a computer that has 6gb ram. The macro turns off screenupdating before it gets to this point.

Is this the most efficient code to delete rows or is a loop better?

Thanks!

 

[Richard Schollar]

That should be fast - do you have lots of formulas in this sheet? Would be worth turning off calculation first as well eg:

Dim xlCalc As xlCalculation

With Application
  xlCalc = .Calculation
  .Calculation = xlCalculationManual
End With

'delete code


Application.Calculation = xlCalc

 

[Tuta]

Exactly four formulas in the entire workbook.

---
I wanted to watch it as it ran through the macro, so I turned the screenupdating on and it worked fine. Stepped through everything.

I then ran through it with screenupdating off and the macro worked fine.

Maybe excel needed to warm up?