Deleting Excel blank Rows

[Karan001]

Hi Experts,
i am trying to delete 20 thousand blank rows from excell sheet but the system is getting hang, is there is any limitation for deleting rows.
Or Is there are some other way to delete unwanted rows in one shot.

 

[Trevor G]

What about filtering on blanks then jsut delete you can do this via code.

Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no data.
'We use Long in case they have over 32,767 rows selected.
Dim i As Long
'We turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

 

[MrKowz]

Try the following code:

Public Sub DeleteBlankRows()
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

Range("A:A").SpecialCells(xlCellTypeBlanks).Delete

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With
End Sub

 

[Rick Rothstein]

@MrKowz,

If I am remembering correctly, your procedure may not work. As I remember it, in versions prior to XL2010, SpecialCells has a limitation of 8192 areas. If the OP's 20,000 rows are divided in such a way that there are 8192 or more individual areas of blank cells, then SpecialCells will fail to work correctly. If there are less areas, then it should work fine.