Tuesday, November 09, 2004

Macro for Deleting Blank Rows in Excel

Following is a macro that will delete blank rows from a selected range in an excel sheet. It looks at a range of multiple rows and columns and only delete the rows that are completely blank. Great for use with imported data from an accounting package or similar that has blank rows in it.

Sub Delete_Blank_Rows()
'Deletes the entire row within the selection if the ENTIRE row contains no data.
'the Long type is used in case there is more than 32,767 rows in the selection

Dim x As Long

'Turn off calculation and screenupdating
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

'work backwards because we are deleting rows and other wise the row number will change.
For x = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(x)) = 0 Then
Selection.Rows(x).EntireRow.Delete
End If
Next x

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

If you want this customised to work with any particular spreadsheet you use then email me.

To use insert into a module in the VBA project editor (ALT + F11) and copy and paste the enitre italic above text in.


Posted by: Jethro | 10:12 AM |