PDA

View Full Version : macro to speed up deleting empty rows process



Pete
12-02-2009, 02:40 AM
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
12-02-2009, 02:54 AM
is it possible to delete based on last entry in column A or modify the macro to do so.....

Bob Phillips
12-02-2009, 02:56 AM
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
12-02-2009, 02:57 AM
BTW, after 240+ posts, isn't it about time you learnt how to add VBA tags.

Pete
12-02-2009, 03:09 AM
apol0gies

Pete
12-02-2009, 04:15 AM
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
12-02-2009, 04:44 AM
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
12-02-2009, 06:48 AM
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.