Excel: Delete/Deleting Blank Rows With Excel VBA

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Conditional Excel Row Deleting-Delete Rows Based on Criteria

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Back to: Excel VBA . Got any Excel/VBA Questions? Free Excel Help

Below are 6 methods that will delete rows from within a selection. If you know the range you can replace "Selection" with your Range(). It is important to note that the least efficient methods involve those that use loops. This is because they only delete one row at a time!

In some examples we turn off Calculation and Screenupdating. The reason we turn off calculation is in case the range in which we are deleting rows contains lots of formulas, if it does Excel may need to recalculate each time a row is deleted, slowing down the macro. The screenupdating being set to false will also speed up our macro as Excel will not try to repaint the screen each time it changes.

Subs: DeleteBlankRows1, DeleteBlankRows3 and both Worksheet_Change events are slightly different as they first check to see if the ENTIRE row is blank.

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

Sub DeleteBlankRows2()
'Deletes the entire row within the selection if _
 some of the cells WITHIN THE SELECTION contain no data.
On Error Resume Next
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Sub DeleteBlankRows3()
'Deletes the entire row within the selection if _
the ENTIRE row contains no data.

Dim Rw As Range
If WorksheetFunction.CountA(Selection) = 0 Then
   MsgBox "No data found", vbOKOnly, "OzGrid.com"
   Exit Sub
End If
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False

    Selection.SpecialCells(xlCellTypeBlanks).Select

        For Each Rw In Selection.Rows
            If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
                Selection.EntireRow.Delete
            End If
        Next Rw

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

Sub MoveBlankRowsToBottom()
'Assumes the list has a heading

	With Selection
		.Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _
			Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
			Orientation:=xlTopToBottom
	End With 
End Sub

Sub DeleteRowsBasedOnCriteria()
'Assumes the list has a heading.
   	With ActiveSheet
             If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
                    .Range("A1").AutoFilter Field:=1, Criteria1:="Delete"
                    .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
			(xlCellTypeVisible).EntireRow.Delete
     		.AutoFilterMode = False
	End With
End Sub

Sub DeleteRowsWithSpecifiedData()
'Looks in Column D and requires Column IV to be clean
	Columns(4).EntireColumn.Insert

	With Range("D1:D" & ActiveSheet.UsedRange.Rows.Count)
 			.FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Not Needed"",NA()))"
			.Value = .Value
			On Error Resume Next
			.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
	End With
	On Error GoTo 0
	Columns(4).EntireColumn.Delete
End Sub

To use any or all of the above code:

Open Excel.
Push Alt+F11 to open the VBE (Visual Basic Editor).
Go to Insert>Module.
Copy the code and paste it in the new module.
Push Alt+Q to return to Excels normal view.
Push Alt+F8 and then select the macro name and click Run. Or select Options and assign a shortcut key.

Removing Blank Rows Automatically

The codes above will work fine for removing blank rows from a list that already has some, but as the saying goes "Prevention is better than cure". The two examples below will remove blank rows as they occur. Either code should be placed within the Worksheet module and will occur each time a cell changes on the worksheet.

In both codes you will notice the Application.EnableEvents=False this is often needed within Event codes like this, else the Event will be triggered again once the code executes which in turn will again trigger the Event and so on.....

You will no doubt also notice the GoTo SelectionCode which occurs if the number of cells within the selection exceeds one. The reason for this is an error would occur if the code reached the Target keyword as Target refers to a single cell.

The second example uses the Sort method rather than the EntireRow.Delete and is the preferred method to use if possible. What happens is, any blank rows are placed at the bottom of the range should the entire row be blank.

The use of the keyword Me is a good habit to get into when working within Worksheet and Workbook modules. This was shown to me by my internet friend from Belgium, Geert Dumortier.

Private Sub Worksheet_Change(ByVal Target As Range)
'Deletes blank rows as they occur.

	'Prevent endless loops
	Application.EnableEvents = False
	'They have more than one cell selected
	If Target.Cells.Count > 1 Then GoTo SelectionCode
		If WorksheetFunction.CountA(Target.EntireRow) = 0 Then
			Target.EntireRow.Delete
		End If

	Application.EnableEvents = True
	'Our code will only enter here if the selection is more than one cell.
	Exit Sub
			
SelectionCode:
	If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
		Selection.EntireRow.Delete
	End If
	Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Sorts blank rows to the bottom as they occur

	'Prevents endless loops
	Application.EnableEvents = False
	'They have more than one cell selected
		If Target.Cells.Count > 1 Then GoTo SelectionCode
			If WorksheetFunction.CountA(Target.EntireRow) <> 0 Then
				Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _
					Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
					Orientation:=xlTopToBottom

		End If
	Application.EnableEvents = True

Exit Sub 'Our code will only enter here if the selection is _
more than one cell.

SelectionCode:
	If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
		Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _
			Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
			Orientation:=xlTopToBottom
	End If
	Application.EnableEvents = True
End Sub

To use either one of the above codes:

Open Excel.
Right click on the Sheet name tab.
Select View Code from the Pop-up menu
Copy the code and paste it over the top of the default Event
Push Alt+Q to return to Excels normal view.
Push Alt+F8 and then select the macro name and click Run. Or select Options and assign a shortcut key.

Of all the examples above that use Excels AutoFilters and Sort are by far the quickest methods I know of. If anybody knows of a quicker way, please tell me.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft