macro to speed up deleting empty rows process

[Pete]

Hi

I currently have the following macro to delete all empty row if a cell in column K is empty is works but tales 25min to run....i have in excess of 90000 row in the worksheet....60% of cells in column K need to be removed....

Any ideas ?

Sub Remove_Empty_Cells()

Dim lngRow As Long
Application.ScreenUpdating = False
For lngRow = Cells(Cells.Rows.Count, "K").End(xlUp).Row To 6 Step -1
If Len(Cells(lngRow, "K").Value) = 0 Then Rows(lngRow).Delete
Next lngRow
Application.ScreenUpdating = True
End Sub

[Pete]

is it possible to delete based on last entry in column A or modify the macro to do so.....

[Bob Phillips]

Sub Remove_Empty_Cells()
Dim rng As Range
Dim lngRow As Long
Application.ScreenUpdating = False
lngRow = Cells(Cells.Rows.Count, "K").End(xlUp).Row
Columns("K").AutoFilter Field:=1, Criteria1:=0
On Error Resume Next
Set rng = Columns("K").Cells(2, 1).Resize(lngRow - 1).SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then rng.EntireRow.Delete
Columns("K").AutoFilter
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

[Bob Phillips]

BTW, after 240+ posts, isn't it about time you learnt how to add VBA tags.

[Pete]

apol0gies

[Pete]

Hi xld...

just test the macro its not remove the empty rows of a cell is empty in column K only based on data held in column A until the last entry point in column A......

Nothing happens and i cannot post or upload sample workook due to data reason........

[Bob Phillips]

Sorri, I misread that test for len of 0 to be a test for 0.

Try this



Sub Remove_Empty_Cells()
Dim rng As Range
Dim lngRow As Long
Application.ScreenUpdating = False
lngRow = Cells(Rows.Count, "K").End(xlUp).Row
On Error Resume Next
Set rng = Cells(1, "K").Resize(lngRow - 1).SpecialCells(xlCellTypeBlanks)
If Not rng Is Nothing Then rng.EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

[mdmackillop]

Hi Pete,
Going back to your original code, to speed up execution, you should also set Calculation to Manual, then back to Automatic on completion. Bob's code should still be quicker though.