Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel: Delete/Deleting Blank Rows With Excel VBA

Conditional Excel Row Deleting-Delete Rows Based on Criteria

Delete rows by condition add-in.

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 Become an ExcelUser Affiliate & Earn Money

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 [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

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