Deleting Blank Rows in Excel

[Mark]

Is there a command I can use to delete all blank rows in
an Excel file?

Thanks.

MJM

 

[Ken Wright]

Assume you were able to say that any cell in Col A that is blank is a row you would like deleted.
Select the data in Col A, do Edit / Go To / Special / Blank cells, then do Edit / Delete Row.

 

[Don Guillett]

An oft asked question that could have been found in the archives.

Sub RowBeGone()
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Ron de Bruin]

Two subs that delete blank rows Mark

First sub:
If your selection have 1 Row it will run it for the Usedrange
If your selection have more then 1 Row it will use that range

The Second sub will use the UsedRange


Public Sub DeleteRealyBlankRows()
'Chip Pearson
Dim R As Long
Dim C As Range
Dim N As Long
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For R = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(R).EntireRow) = 0 Then
rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Next R
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub DeleteEmptyRows()
'JW
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub

 

[Ron de Bruin]

Don his sub is only check column A and not a whole row
Maybe good for you??

Better to use a on error in your code also

On Error Resume Next 'In case there are no blank rows
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

 

[rjb]

I typically select one column with the blank cells.
Then Select Data/Filter/AutoFilter
Select the drop down on the cell and choose Blanks
Select blanks rows and delete.

Rick