Excel: Delete/Deleting Blank Rows With Excel VBA
Category: [Excel] Demo Available
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.
See also:
| Index to Excel VBA Code |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
LocksmithsLocator lock repair services are provided by highly qualified specialists. Each of these specialists is necessarily trained by OzGrid.