In this VBA Tutorial, you learn how to use Excel VBA to delete rows based on a variety of criteria.
This VBA Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. You can get immediate free access to these example workbooks by subscribing to the Power Spreadsheets Newsletter.
Use the following Table of Contents to navigate to the section you're interested in.
Table of Contents
Related VBA and Macro Tutorials
The following VBA and Macro Tutorials may help you better understand and implement the contents below.
- General VBA constructs and structures:
- Practical VBA applications and macro examples:
You can find additional VBA and Macro Tutorials in the Archives.
#1: Delete a Row
VBA Code to Delete a Row
To delete a row using VBA, use a statement with the following structure:
Worksheets.Rows(Row#).Delete
Process Followed by VBA Code
VBA Statement Explanation
Worksheets.Rows(Row#).Delete
- Item: Worksheets.
- VBA Construct: Workbook.Worksheets property.
- Description: Returns a Worksheet object representing the worksheet you work with.
- VBA Construct: Workbook.Worksheets property.
- Item: Rows(Row#).
- VBA Construct: Worksheet.Rows property.
- Description: Returns a Range object representing row number Row# of the worksheet returned by item #1 above.
If you explicitly declare a variable to represent Row#, use the Long data type.
- VBA Construct: Worksheet.Rows property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #2 above.
- VBA Construct: Range.Delete method.
Macro Example
The following macro deletes row 6 of the worksheet named “Delete row”.
Sub deleteRow() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Worksheets("Delete row").Rows(6).Delete End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, VBA deletes row 6 of the worksheet.
#2: Delete a Row and Shift Up
VBA Code to Delete a Row and Shift Up
To delete a row and explicitly shift cells up to replace the deleted row, use a statement with the following structure:
Worksheet.Rows(Row#).Delete Shift:=xlShiftUp
Process Followed by VBA Code
VBA Statement Explanation
Worksheet.Rows(Row#).Delete Shift:=xlShiftUp
- Item: Worksheet.
- VBA Construct: Workbook.Worksheets property.
- Description: Returns a Worksheet object representing the worksheet you work with.
- VBA Construct: Workbook.Worksheets property.
- Item: Rows(Row#).
- VBA Construct: Worksheet.Rows property.
- Description: Returns a Range object representing row number Row# of the worksheet returned by item #1 above.
If you explicitly declare a variable to represent Row#, use the Long data type.
- VBA Construct: Worksheet.Rows property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #2 above.
- VBA Construct: Range.Delete method.
- Item: Shift:=xlShiftUp.
- VBA Construct: Shift parameter of the Range.Delete method.
- Description:
- Shifts rows up (xlShiftUp) to replace the deleted row.
- You can usually omit this parameter. By default, VBA decides how to shift the cells based on the range's shape. When deleting a row, this generally results in Excel shifting the cells up.
- Shifts rows up (xlShiftUp) to replace the deleted row.
- VBA Construct: Shift parameter of the Range.Delete method.
Macro Example
The following macro deletes row 10 of the worksheet named “Delete row” and explicitly specifies to shift cells up to replace the deleted row.
Sub deleteRowShiftUp() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Worksheets("Delete row").Rows(10).Delete Shift:=xlShiftUp End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, VBA deletes row 10 of the worksheet and shifts cells up to replace the deleted row.
#3: Delete Multiple Rows
VBA Code to Delete Multiple Rows
To delete multiple rows, use a statement with the following structure:
Worksheet.Rows("FirstRow#:LastRow#").Delete
Process Followed by VBA Code
VBA Statement Explanation
Worksheet.Rows(“FirstRow#:LastRow#”).Delete
- Item: Worksheet.
- VBA Construct: Workbook.Worksheets property.
- Description: Returns a Worksheet object representing the worksheet you work with.
- VBA Construct: Workbook.Worksheets property.
- Item: Rows(“FirstRow#:LastRow#”).
- VBA Construct: Worksheet.Rows property.
- Description: Returns a Range object representing rows number FirstRow# through LastRow# of the worksheet returned by item #1 above.
- VBA Construct: Worksheet.Rows property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #2 above.
- VBA Construct: Range.Delete method.
Macro Example
The following macro deletes rows 14 to 18 of the worksheet named “Delete row”.
Sub deleteMultipleRows() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Worksheets("Delete row").Rows("14:18").Delete End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, VBA deletes rows 14 to 18 of the worksheet.
#4: Delete Selected Row
VBA Code to Delete Selected Row
To delete the selected row (the row containing the active cell), use the following statement:
ActiveCell.EntireRow.Delete
Process Followed by VBA Code
VBA Statement Explanation
ActiveCell.EntireRow.Delete
- Item: ActiveCell.
- VBA Construct: Application.ActiveCell property.
- Description: Returns a Range object representing the active cell.
- VBA Construct: Application.ActiveCell property.
- Item: EntireRow.
- VBA Construct: Range.EntireRow property.
- Description: Returns a Range object representing the entire row containing the cell range returned by item #1 above.
- VBA Construct: Range.EntireRow property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #2 above.
- VBA Construct: Range.Delete method.
Macro Example
The following macro deletes the selected row (the row containing the active cell):
Sub deleteSelectedRow() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ ActiveCell.EntireRow.Delete End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. When I execute the macro, the active cell is B20. As expected, Excel deletes the selected row.
#5: Delete Multiple Selected Rows
VBA Code to Delete Multiple Selected Rows
To delete multiple selected rows, use the following statement:
Selection.EntireRow.Delete
Process Followed by VBA Code
VBA Statement Explanation
Selection.EntireRow.Delete
- Item: Selection.
- VBA Construct: Application.Selection property.
- Description: Returns a Range object representing the current cell range selection.
- VBA Construct: Application.Selection property.
- Item: EntireRow.
- VBA Construct: Range.EntireRow property.
- Description: Returns a Range object representing the entire row containing the Range object returned by item #1 above.
- VBA Construct: Range.EntireRow property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #2 above.
- VBA Construct: Range.Delete method.
Macro Example
The following macro deletes the (multiple) selected rows.
Sub deleteSelectedRows() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Selection.EntireRow.Delete End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. When I execute the macro, the selected cells are B24 to B28. As expected, Excel deletes the selected rows.
#6: Delete Blank Rows
VBA Code to Delete Blank Rows
To delete blank rows, use a macro with the following statement structure:
With Worksheet For Counter = LastRow To FirstRow Step -1 If WorksheetFunction.CountA(.Rows(Counter)) = 0 Then If Not BlankRows Is Nothing Then Set BlankRows = Union(BlankRows, .Rows(Counter)) Else Set BlankRows = .Rows(Counter) End If End If Next Counter End With If Not BlankRows Is Nothing Then BlankRows.Delete
Process Followed by VBA Code
VBA Statement Explanation
Lines #1 and #11: With Worksheet | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: Statements within the With… End With statement (lines #2 through #10 below) are executed on the worksheet returned by item #2 below.
- VBA Construct: With… End With statement.
- Item: Worksheet.
- VBA Construct: Workbook.Worksheets property.
- Description: Returns a Worksheet object representing the worksheet you work with.
- VBA Construct: Workbook.Worksheets property.
Lines #2 and #10: For Counter = LastRow To FirstRow Step -1 | Next Counter
- Item: For… Next Counter.
- VBA Construct: For… Next statement.
- Description: Repeats the statements within the loop (lines #3 through #9 below) for each row between (and including) FirstRow (item #4 below) and LastRow (item #3 below).
- VBA Construct: For… Next statement.
- Item: Counter.
- VBA Construct: Counter of For… Next statement.
- Description: Loop counter. If you explicitly declare a variable to represent the loop counter, use the Long data type.
- VBA Construct: Counter of For… Next statement.
- Item: LastRow.
- VBA Construct: Counter Start of For… Next statement.
- Description: Number of the last row (further down the worksheet) you want the macro to consider when identifying blank rows. The number of the last row is also the initial value of Counter (item #2 above).
If you explicitly declare a variable to represent the number of the last row to consider, use the Long data type.
- VBA Construct: Counter Start of For… Next statement.
- Item: FirstRow.
- VBA Construct: Counter End of For… Next statement.
- Description: Number of the first row (closer to the top of the worksheet) you want the macro to consider when identifying blank rows. The number of the first row is also the final value of Counter (item (#2 above).
If you explicitly declare a variable to represent the number of the first row to consider, use the Long data type.
- VBA Construct: Counter End of For… Next statement.
- Item: Step -1.
- VBA Construct: Step of For… Next statement.
- Description: Amount by which Counter (item #2 above) changes every time a loop iteration occurs.
In this scenario, you loop backwards: from LastRow (item #3 above) to FirstRow (item #4 above). Therefore, step is -1.
- VBA Construct: Step of For… Next statement.
Line #3: If WorksheetFunction.CountA(.Rows(Counter)) = 0 Then
- Item: If… Then.
- VBA Construct: Opening line of If… Then… Else statement.
- Description: Conditionally executes the statements within the If… Then block (lines #4 through #8 below) if the condition specified by item #4 below is met.
- VBA Construct: Opening line of If… Then… Else statement.
- Item: WorksheetFunction.CountA.
- VBA Construct: WorksheetFunction.CountA method.
- Description: Counts the number of cells that aren't empty in the range returned by item #3 below.
Since the range returned by item #3 below represents the row through which the macro is currently looping, Worksheet.CountA counts the number of cells that aren't empty in that row.
- VBA Construct: WorksheetFunction.CountA method.
- Item: .Rows(Counter).
- VBA Construct: Worksheet.Rows property.
- Description: Returns a Range object representing the row through which the macro is currently looping.
- VBA Construct: Worksheet.Rows property.
- Item: WorksheetFunction.CountA(.Rows(Counter)) = 0.
- VBA Construct: Condition of If… Then… Else statement.
- Description: This condition is a numeric expression that evaluates to True or False, as follows:
- True: When the WorksheetFunction.CountA method (item #2 above) returns 0. This happens when the row through which the macro is currently looping (item #3 above) is empty and, therefore, the number of non-empty cells is 0.
- False: When WorksheetFunction.CountA returns a number other than 0. This happens when the row through which the macro is currently looping isn't empty and, therefore, the number of non-empty cells isn't 0.
- True: When the WorksheetFunction.CountA method (item #2 above) returns 0. This happens when the row through which the macro is currently looping (item #3 above) is empty and, therefore, the number of non-empty cells is 0.
- VBA Construct: Condition of If… Then… Else statement.
Line #4: If Not BlankRows Is Nothing Then
- Item: If… Then.
- VBA Construct: Opening line of If… Then… Else statement.
- Description: Conditionally executes the statement within the If… Then… Else block (line #5 below) if the condition specified by item #6 below is met.
- VBA Construct: Opening line of If… Then… Else statement.
- Item: Not.
- VBA Construct: Not operator.
- Description: Carries out a logical negation on item #3 below. In other words, if item #3 returns:
- True, the result is False.
- False, the result is True.
- True, the result is False.
- VBA Construct: Not operator.
- Item: BlankRows.
- VBA Construct: Object (Range) variable.
- Description: Holds a Range object representing the empty rows found by the macro.
- VBA Construct: Object (Range) variable.
- Item: Is.
- VBA Construct: Is Operator.
- Description: Compares 2 object reference variables: (i) Not BlankRows (items #2 and #3 above) vs. (ii) Nothing (item #5 below).
If both object references refer to the same object, the Is operator returns True. If they refer to different objects, Is returns False.
- VBA Construct: Is Operator.
- Item: Nothing.
- Description: The default value for a data type. In the case of an object variable (such as BlankRows), a null reference.
- Description: The default value for a data type. In the case of an object variable (such as BlankRows), a null reference.
- Item: Not BlankRows Is Nothing.
- VBA Construct: Condition of If… Then… Else statement.
- Description: The condition is an expression that evaluates to True or False, as follows:
- True: When “Not BlankRows” (items #2 and #3 above) refers to the same object as Nothing (item #5 above). This happens when BlankRows is “something”.
Since BlankRows holds a Range object representing cell ranges within the empty rows found by the macro, BlankRows is something if the macro finds at least one such row. - False: When “Not BlankRows” refers to a different object from Nothing. This happens when BlankRows itself is Nothing. This occurs prior to the macro finding the first empty row. This is because BlankRows isn't assigned to anything prior to that moment.
- True: When “Not BlankRows” (items #2 and #3 above) refers to the same object as Nothing (item #5 above). This happens when BlankRows is “something”.
- VBA Construct: Condition of If… Then… Else statement.
Line #5: Set BlankRows = Union(BlankRows, .Rows(Counter))
- Item: Set… =.
- VBA Construct: Set statement.
- Description: Assigns the object reference returned by item #6 below to BlankRows (item #2 below).
- VBA Construct: Set statement.
- Item: BlankRows.
- VBA Construct: Object (Range) variable of Set statement.
- Description:
- Holds a Range object representing the empty rows found by the macro.
- BlankRows is included twice in the statement. In the first mention (Set BlankRows), BlankRows is the object variable to which an object reference is assigned.
- Holds a Range object representing the empty rows found by the macro.
- VBA Construct: Object (Range) variable of Set statement.
- Item: Union.
- VBA Construct: Application.Union method.
- Description: Returns a Range object representing the union of the Range objects returned by items #4 and #5 below.
- VBA Construct: Application.Union method.
- Item: BlankRows.
- VBA Construct: Object (Range) variable.
- Description:
- Holds a Range object representing the empty rows found by the macro.
- BlankRows is included twice in the statement. In the second mention (Union(BlankRows, .Rows(Counter)), BlankRows is one of the parameters of the Application.Union method.
- Holds a Range object representing the empty rows found by the macro.
- VBA Construct: Object (Range) variable.
- Item: .Rows(Counter).
- VBA Construct: Worksheet.Rows property.
- Description: Returns a Range object representing the row through which the macro is currently looping.
- VBA Construct: Worksheet.Rows property.
- Item: Union(BlankRows, .Rows(Counter).
- VBA Construct: Object expression of Set statement.
- Description: Returns the new Range object reference assigned to the BlankRows object variable (item #2 above). This is the union of the following 2 Range objects:
- Prior to the Set statement, BlankRows represents cell ranges within the empty rows found by the macro prior to the row through which it's currently looping.
- “.Rows(Counter)” represents the row through which the macro is currently looping.
Graphically, this looks as follows:
In other words, any empty row the macro finds is “added” to BlankRows. - Prior to the Set statement, BlankRows represents cell ranges within the empty rows found by the macro prior to the row through which it's currently looping.
- VBA Construct: Object expression of Set statement.
Line #6: Else
- Item: Else.
- VBA Construct: Else clause of If… Then… Else statement.
- Description: The statement following the Else clause (line #7 below) is executed if the condition tested in the opening line of the If… Then… Else statement (line #4 above) isn't met and returns False.
- VBA Construct: Else clause of If… Then… Else statement.
Line #7: Set BlankRows = .Rows(Counter)
- Item: Set… =.
- VBA Construct: Set statement.
- Description: Assigns the object reference returned by item #3 below to BlankRows (item #2 below).
- VBA Construct: Set statement.
- Item: BlankRows.
- VBA Construct: Object (Range) variable of Set statement.
- Description: Holds a Range object representing the empty rows found by the macro.
- VBA Construct: Object (Range) variable of Set statement.
- Item: .Rows(Counter).
- VBA Construct: Worksheet.Rows property.
- Description: Returns a Range object representing the row through which the macro is currently looping.
- VBA Construct: Worksheet.Rows property.
Lines #8 and #9: End If | End If
- Item: End If.
- VBA Construct: Closing lines of If… Then… Else statements.
- Description: Ends the If… Then… Else statements that began in lines #3 and #4 above.
- VBA Construct: Closing lines of If… Then… Else statements.
Line #12: If Not BlankRows Is Nothing Then BlankRows.Delete
- Item: If… Then.
- VBA Construct: If… Then… Else statement.
- Description: Conditionally executes the statement at the end of the line (items #7 and #8 below) if the condition specified by item #6 below is met.
- VBA Construct: If… Then… Else statement.
- Item: Not.
- VBA Construct: Not operator.
- Description: Carries out a logical negation on item #3 below. In other words, if item #3 returns:
- True, the result is False.
- False, the result is True.
- True, the result is False.
- VBA Construct: Not operator.
- Item: BlankRows.
- VBA Construct: Object (Range) variable.
- Description: Holds a Range object representing the empty rows found by the macro.
- VBA Construct: Object (Range) variable.
- Item: Is.
- VBA Construct: Is Operator.
- Description: Compares 2 object reference variables: (i) Not BlankRows (items #2 and #3 above) vs. (ii) Nothing (item #5 below).
If both object references refer to the same object, the Is operator returns True. If they refer to different objects, Is returns False.
- VBA Construct: Is Operator.
- Item: Nothing.
- Description: The default value for a data type. In the case of an object variable (such as BlankRows), a null reference.
- Description: The default value for a data type. In the case of an object variable (such as BlankRows), a null reference.
- Item: Not BlankRows Is Nothing.
- VBA Construct: Condition of If… Then… Else statement.
- Description: The condition is an expression that evaluates to True or False, as follows:
- True: When “Not BlankRows” (items #2 and #3 above) refers to the same object as Nothing (item #5 above). This happens when BlankRows is “something”.
Since BlankRows holds a Range object representing cell ranges within the empty rows found by the macro, BlankRows is something if the macro has found at least 1 empty row. - False: When “Not BlankRows” refers to a different object from Nothing. This happens when BlankRows itself is Nothing. This, in turn, occurs when the macro founds no empty rows.
- True: When “Not BlankRows” (items #2 and #3 above) refers to the same object as Nothing (item #5 above). This happens when BlankRows is “something”.
- VBA Construct: Condition of If… Then… Else statement.
- Item: BlankRows.
- VBA Construct: Object (Range) variable.
- Description: Holds a Range object representing the empty rows found by the macro.
- VBA Construct: Object (Range) variable.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #7 above.
- VBA Construct: Range.Delete method.
Macro Example
The following macro deletes all blank rows between rows number myFirstRow and LastRow.
- myFirstRow is set to 6.
- myLastRow is set to the number of the last row with data in the worksheet named “Delete empty rows”. The constructs used by the statement that finds the last row with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Row property.
Sub deleteEmptyRows() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Dim myFirstRow As Long Dim myLastRow As Long Dim myWorksheet As Worksheet Dim iCounter As Long Dim myBlankRows As Range myFirstRow = 6 Set myWorksheet = Worksheets("Delete empty rows") With myWorksheet myLastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For iCounter = myLastRow To myFirstRow Step -1 If WorksheetFunction.CountA(.Rows(iCounter)) = 0 Then If Not myBlankRows Is Nothing Then Set myBlankRows = Union(myBlankRows, .Rows(iCounter)) Else Set myBlankRows = .Rows(iCounter) End If End If Next iCounter End With If Not myBlankRows Is Nothing Then myBlankRows.Delete End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, VBA deletes all blank rows between row 6 and the last row with data on the worksheet.
#7: Delete Rows with Blank Cells
VBA Code to Delete Rows with Blank Cells
To delete rows with blank cells using VBA, use a macro with the following statement structure:
With Worksheet Set RangeForCriteria = .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn)) End With On Error Resume Next RangeForCriteria.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Process Followed by VBA Code
VBA Statement Explanation
Lines #1 and #3: With Worksheet | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: The statement within the With… End With statement (line #2 below) is executed on the worksheet returned by item #2 below.
- VBA Construct: With… End With statement.
- Item: Worksheet.
- VBA Construct: Workbook.Worksheets property.
- Description: Returns a Worksheet object representing the worksheet you work with.
- VBA Construct: Workbook.Worksheets property.
Line #2: Set RangeForCriteria = .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn))
- Item: Set… =.
- VBA Construct: Set statement.
- Description: Assigns the object reference returned by items #3 through #5 below to RangeForCriteria (item #2 below).
- VBA Construct: Set statement.
- Item: RangeForCriteria.
- VBA Construct: Object (Range) variable.
- Description: Holds a Range object representing the cell range you want the macro to search for blank cells.
- VBA Construct: Object (Range) variable.
- Item: .Range.
- VBA Construct: Worksheet.Range property.
- Description: Returns a Range object representing a cell range specified as follows:
- Upper-left corner cell: Range object returned by item #4 below.
- Lower-right corner cell: Range object returned by item #5 below.
- Upper-left corner cell: Range object returned by item #4 below.
- VBA Construct: Worksheet.Range property.
- Item: .Cells(FirstRow, FirstColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number FirstRow and column number FirstColumn.
FirstRow and FirstColumn are the number of, respectively, the first row and first column in the cell range you want the macro to search for blank cells. If you explicitly declare a variable to represent FirstRow or FirstColumn, use the Long data type.
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Item: .Cells(LastRow, LastColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number LastRow and column number LastColumn.
LastRow and LastColumn are the number of, respectively, the last row and last column in the cell range you want the macro to search for blank cells. If you explicitly declare a variable to represent LastRow or LastColumn, use the Long data type.
- VBA Construct: Worksheet.Cells property and Range.Item property.
Line #4: On Error Resume Next
- Item: On Error Resume Next.
- VBA Construct: On Error Resume Next statement.
- Description: Specifies that, when a run-time error occurs, control goes to the statement following the statement where the error occurs.
The error-handler in this line #4 is necessary because, if the cell range you want the macro to search for blank cells doesn't contain any such cells, line #5 below generates a run-time error.
- VBA Construct: On Error Resume Next statement.
Line #5: RangeForCriteria.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
- Item: RangeForCriteria.
- VBA Construct: Object (Range) variable.
- Description: Holds a Range object representing the cell range you want the macro to search for blank cells.
- VBA Construct: Object (Range) variable.
- Item: SpecialCells(xlCellTypeBlanks).
- VBA Construct: Range.SpecialCells method and Type parameter of Range.SpecialCells method.
- Description: Returns a Range object representing all empty cells within the cell range returned by RangeForCriteria (item #1 above).
- VBA Construct: Range.SpecialCells method and Type parameter of Range.SpecialCells method.
- Item: EntireRow.
- VBA Construct: Range.EntireRow property.
- Description: Returns a Range object representing the entire rows containing the Range object returned by item #2 above.
- VBA Construct: Range.EntireRow property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #3 above.
- VBA Construct: Range.Delete method.
Macro Example
The following macro deletes all rows with blank cells between:
- Rows number myFirstRow and myLastRow.
- Columns number myFirstColumn and myLastColumn.
In this example:
- myFirstRow is set to 6.
- myFirstColumn is set to 2.
- myLastRow is set to the number of the last row with data in the worksheet named “Delete row with blank cells”. The constructs used by the statement that finds the last row with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Row property.
- myLastColumn is set to the number of the last column with data in the same worksheet. The constructs used by the statement that finds the last column with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Column property.
Sub deleteRowBlankCells() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Dim myFirstRow As Long Dim myLastRow As Long Dim myFirstColumn As Long Dim myLastColumn As Long Dim myWorksheet As Worksheet Dim myRange As Range myFirstRow = 6 myFirstColumn = 2 Set myWorksheet = Worksheets("Delete row with blank cells") With myWorksheet With .Cells myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column End With Set myRange = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) End With On Error Resume Next myRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, VBA deletes all rows with blank cells between (i) row 6 and the last row with data on the worksheet, and (ii) column 2 and the last column with data on the worksheet.
#8: Delete Rows with Blank Cells in a Specific Column
VBA Code to Delete Rows with Blank Cells in a Specific Column
To delete rows with blank cells in a specific column using VBA, use a macro with the following statement structure:
With Worksheet With .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn)) .AutoFilter Field:=CriteriaField, Criteria1:="=" On Error Resume Next .Offset(RowOffset:=1).Resize(RowSize:=(.Rows.Count - 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With .AutoFilterMode = False End With
Process Followed by VBA Code
VBA Statement Explanation
Lines #1 and #8: With Worksheet | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: Statements within the With… End With statement (lines #2 through #7 below) are executed on the worksheet returned by item #2 below.
- VBA Construct: With… End With statement.
- Item: Worksheet.
- VBA Construct: Workbook.Worksheets property.
- Description: Returns a Worksheet object representing the worksheet you work with.
- VBA Construct: Workbook.Worksheets property.
Lines #2 and #6: With .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn)) | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: Statements within the With… End With statement (lines #3 through #6 below) are executed on the range object returned by items #2 through #4 below.
- VBA Construct: With… End With statement.
- Item: .Range.
- VBA Construct: Worksheet.Range property.
- Description: Returns a Range object representing a cell range specified as follows:
- Upper-left corner cell: Range object returned by item #3 below.
- Lower-right corner cell: Range object returned by item #4 below.
- Upper-left corner cell: Range object returned by item #3 below.
- VBA Construct: Worksheet.Range property.
- Item: .Cells(FirstRow, FirstColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number FirstRow and column number FirstColumn.
FirstRow and FirstColumn are the number of, respectively, the first row and first column in the cell range you work with. If you explicitly declare a variable to represent FirstRow or FirstColumn, use the Long data type.
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Item: .Cells(LastRow, LastColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number LastRow and column number LastColumn.
LastRow and LastColumn are the number of, respectively, the last row and last column in the cell range you work with. If you explicitly declare a variable to represent LastRow or LastColumn, use the Long data type.
- VBA Construct: Worksheet.Cells property and Range.Item property.
Line #3: .AutoFilter Field:=CriteriaField, Criteria1:=”=”
- Item: .AutoFilter.
- VBA Construct: Range.AutoFilter method.
- Description: Filter the data within the range you work with using the AutoFilter and according to the parameters specified by items #2 and #3 below.
- VBA Construct: Range.AutoFilter method.
- Item: Field:=CriteriaField.
- VBA Construct: Field parameter of Range.AutoFilter method.
- Description: Specifies the field on which you want to base the filter. The leftmost field of the range you work with is Field 1. The rightmost field is the number of fields in the cell range you work with.
If you explicitly declare a variable to represent CriteriaField, use the Long data type.
- VBA Construct: Field parameter of Range.AutoFilter method.
- Item: Criteria1:=”=”.
- VBA Construct: Criteria1 parameter of Range.AutoFilter method.
- Description: Specifies the filtering criteria. “=” finds blank cells.
- VBA Construct: Criteria1 parameter of Range.AutoFilter method.
Line #4: On Error Resume Next
- Item: On Error Resume Next.
- VBA Construct: On Error Resume Next statement.
- Description: Specifies that, when a run-time error occurs, control goes to the statement following the statement where the error occurs.
The error-handler in this line #4 is necessary because, if the field you filter by (line #3 above) doesn't contain blank cells, line #5 below generates a run-time error.
- VBA Construct: On Error Resume Next statement.
Line #5: .Offset(RowOffset:=1).Resize(RowSize:=(.Rows.Count – 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
- Item: Offset.
- VBA Construct: Range.Offset property.
- Description: Returns a Range object a number of rows above or below the cell range it works with, as returned by line #2 above.
- VBA Construct: Range.Offset property.
- Item: RowOffset:=1.
- VBA Construct: RowOffset parameter of Range.Offset property.
- Description: Specifies that the cell range returned by Range.Offset (item #1 above) is 1 row below the range specified in line #2 above.
Line #2 above specifies the cell range you work with. Therefore, the Range object that Range.Offset returns has the same size but is 1 row below the cell range you work with. This results in the following:
- The headers of the cell range you work with are excluded from the Range object.
- The first empty row below the last row with data (LastRow in line #2 above) is included. This extra line is handled by item #7 below.
- The headers of the cell range you work with are excluded from the Range object.
- VBA Construct: RowOffset parameter of Range.Offset property.
- Item: Resize.
- VBA Construct: Range.Resize property.
- Description: Resizes the cell range returned by items #1 and #2 above.
- VBA Construct: Range.Resize property.
- Item: RowSize.
- VBA Construct: RowSize parameter of Range.Resize property.
- Description: Specifies the number of rows in the new cell range returned by Range.Resize (item #3 above).
- VBA Construct: RowSize parameter of Range.Resize property.
- Item: Rows.
- VBA Construct: Range.Rows property.
- Description: Returns a Range object representing the rows in the cell range it works with, as returned by line #2 above.
- VBA Construct: Range.Rows property.
- Item: Count.
- VBA Construct: Range.Count property.
- Description: Returns the number of rows within the Range object returned by item #5 above.
- VBA Construct: Range.Count property.
- Item: Resize(RowSize:=(.Rows.Count – 1)).
- VBA Construct: Range.Resize property.
- Description: Resizes the cell range returned by items #1 and #2 above to reduce it by one row. The number of rows in the new range is obtained by subtracting 1 from the number of rows returned by line #2, as counted by items #5 and #6 above.
This results in a cell range that excludes the first empty row below the last row with data that the Range.Offset property (items #1 and #2 above) included.
- VBA Construct: Range.Resize property.
- Item: SpecialCells(xlCellTypeVisible).
- VBA Construct: Range.SpecialCells method and Type parameter of Range.SpecialCells method.
- Description: Returns a Range object representing all visible cells within the cell range you work with, excluding the headers (as required by item #2 above).
Since line #3 above filters the data according to the criteria you specify, the visible cells returned by Range.SpecialCells are those containing blank cells in the column (field) you specify.
- VBA Construct: Range.SpecialCells method and Type parameter of Range.SpecialCells method.
- Item: EntireRow.
- VBA Construct: Range.EntireRow property.
- Description: Returns a Range object representing the entire rows containing the Range object returned by item #8 above.
- VBA Construct: Range.EntireRow property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #10 above.
- VBA Construct: Range.Delete method.
Line #7: .AutoFilterMode = False
- Item: .AutoFilterMode = False.
- VBA Construct: Worksheet.AutoFilterMode property.
- Description: Specifies that the AutoFilter drop-down arrows aren't displayed on the worksheet.
- VBA Construct: Worksheet.AutoFilterMode property.
Macro Example
The following macro deletes all rows that meet the following conditions:
- Are between:
- Rows number (myFirstRow + 1) and myLastrow.
- Columns number myFirstColumn and myLastColumn.
- Rows number (myFirstRow + 1) and myLastrow.
- Contain a blank cell in field number myCriteriaField.
In this example:
- myFirstRow is set to 5.
- myFirstColumn is set to 2.
- myCriteriaField is set to 1.
- myLastRow is set to the number of the last row with data in the worksheet named “Delete row if cell is blank”. The constructs used by the statement that finds the last row with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Row property.
- myLastColumn is set to the number of the last column with data in the same worksheet. The constructs used by the statement that finds the last column with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Column property.
Sub deleteRowBlankCell() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Dim myFirstRow As Long Dim myLastRow As Long Dim myFirstColumn As Long Dim myLastColumn As Long Dim myCriteriaField As Long Dim myWorksheet As Worksheet myFirstRow = 5 myFirstColumn = 2 myCriteriaField = 1 Set myWorksheet = Worksheets("Delete row if cell is blank") With myWorksheet With .Cells myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column End With With .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) .AutoFilter Field:=myCriteriaField, Criteria1:="=" On Error Resume Next .Offset(RowOffset:=1).Resize(RowSize:=(.Rows.Count - 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With .AutoFilterMode = False End With End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, VBA deletes the rows containing blank cells in myCriteriaField (1).
#9: Delete Rows Containing Strings
VBA Code to Delete Rows Containing Strings
To delete rows containing strings using VBA, use a macro with the following statement structure:
With Worksheet Set RangeForCriteria = .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn)) End With On Error Resume Next RangeForCriteria.SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
Process Followed by VBA Code
VBA Statement Explanation
Lines #1 and #3: With Worksheet | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: The statement within the With… End With statement (line #2 below) is executed on the worksheet returned by item #2 below.
- VBA Construct: With… End With statement.
- Item: Worksheet.
- VBA Construct: Workbook.Worksheets property.
- Description: Returns a Worksheet object representing the worksheet you work with.
- VBA Construct: Workbook.Worksheets property.
Line #2: Set RangeForCriteria = .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn))
- Item: Set… =.
- VBA Construct: Set statement.
- Description: Assigns the object reference returned by items #3 through #5 below to RangeForCriteria (item #2 below).
- VBA Construct: Set statement.
- Item: RangeForCriteria.
- VBA Construct: Object (Range) variable.
- Description: Holds a Range object representing the cell range you want the macro to search for cells containing strings.
- VBA Construct: Object (Range) variable.
- Item: .Range.
- VBA Construct: Worksheet.Range property.
- Description: Returns a Range object representing a cell range specified as follows:
- Upper-left corner cell: Range object returned by item #4 below.
- Lower-right corner cell: Range object returned by item #5 below.
- Upper-left corner cell: Range object returned by item #4 below.
- VBA Construct: Worksheet.Range property.
- Item: .Cells(FirstRow, FirstColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number FirstRow and column number FirstColumn.
FirstRow and FirstColumn are the number of, respectively, the first row and first column in the cell range you want the macro to search for cells containing strings. If you explicitly declare a variable to represent FirstRow or FirstColumn, use the Long data type.
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Item: .Cells(LastRow, LastColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number LastRow and column number LastColumn.
LastRow and LastColumn are the number of, respectively, the last row and last column in the cell range you want the macro to search for cells containing strings. If you explicitly declare a variable to represent LastRow or LastColumn, use the Long data type.
- VBA Construct: Worksheet.Cells property and Range.Item property.
Line #4: On Error Resume Next
- Item: On Error Resume Next.
- VBA Construct: On Error Resume Next statement.
- Description: Specifies that, when a run-time error occurs, control goes to the statement following the statement where the error occurs.
The error-handler in this line #4 is necessary because, if the cell range you want the macro to search for cells containing strings doesn't contain any such cells, line #5 below generates a run-time error.
- VBA Construct: On Error Resume Next statement.
Line #5: RangeForCriteria.SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
- Item: RangeForCriteria.
- VBA Construct: Object (Range) variable.
- Description: Holds a Range object representing the cell range you want the macro to search for cells containing strings.
- VBA Construct: Object (Range) variable.
- Item: SpecialCells(xlCellTypeConstants, xlTextValues).
- VBA Construct: Range.SpecialCells method, Type and Value parameters of Range.SpecialCells method.
- Description: Returns a Range object representing all cells containing constant (xlCellTypeConstants) text values (xlTextValues) within the cell range returned by RangeForCriteria (item #1 above). Those are the cells containing strings.
- VBA Construct: Range.SpecialCells method, Type and Value parameters of Range.SpecialCells method.
- Item: EntireRow.
- VBA Construct: Range.EntireRow property.
- Description: Returns a Range object representing the entire rows containing the Range object returned by item #2 above.
- VBA Construct: Range.EntireRow property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #3 above.
- VBA Construct: Range.Delete method.
Macro Example
The following macro deletes all rows containing strings between:
- Rows number myFirstRow and myLastRow.
- Columns number myFirstColumn and myLastColumn.
In this example:
- myFirstRow is set to 6.
- myFirstColumn is set to 2.
- myLastRow is set to the number of the last row with data in the worksheet named “Delete rows containing strings”. The constructs used by the statement that finds the last row with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Row property.
- myLastColumn is set to the number of the last column with data in the same worksheet. The constructs used by the statement that finds the last column with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Column property.
Sub deleteRowContainingStrings() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Dim myFirstRow As Long Dim myLastRow As Long Dim myFirstColumn As Long Dim myLastColumn As Long Dim myWorksheet As Worksheet Dim myRange As Range myFirstRow = 6 myFirstColumn = 2 Set myWorksheet = Worksheets("Delete rows containing strings") With myWorksheet With .Cells myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column End With Set myRange = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) End With On Error Resume Next myRange.SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, VBA deletes all rows containing strings between (i) row 6 and the last row with data on the worksheet, and (ii) column 2 and the last column with data on the worksheet.
#10: Delete Row Based on Cell Value
VBA Code to Delete Row Based on Cell Value
To delete rows based on the value in a specific cell using VBA, use a macro with the following statement structure:
With Worksheet With .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn)) .AutoFilter Field:=CriteriaField, Criteria1:=Value On Error Resume Next .Offset(RowOffset:=1).Resize(RowSize:=(.Rows.Count - 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With .AutoFilterMode = False End With
Process Followed by VBA Code
VBA Statement Explanation
Lines #1 and #8: With Worksheet | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: Statements within the With… End With statement (lines #2 through #7 below) are executed on the worksheet returned by item #2 below.
- VBA Construct: With… End With statement.
- Item: Worksheet.
- VBA Construct: Workbook.Worksheets property.
- Description: Returns a Worksheet object representing the worksheet you work with.
- VBA Construct: Workbook.Worksheets property.
Lines #2 and #6: With .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn)) | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: Statements within the With… End With statement (lines #3 through #6 below) are executed on the range object returned by items #2 through #4 below.
- VBA Construct: With… End With statement.
- Item: .Range.
- VBA Construct: Worksheet.Range property.
- Description: Returns a Range object representing a cell range specified as follows:
- Upper-left corner cell: Range object returned by item #3 below.
- Lower-right corner cell: Range object returned by item #4 below.
- Upper-left corner cell: Range object returned by item #3 below.
- VBA Construct: Worksheet.Range property.
- Item: .Cells(FirstRow, FirstColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number FirstRow and column number FirstColumn.
FirstRow and FirstColumn are the number of, respectively, the first row and first column in the cell range you work with. If you explicitly declare a variable to represent FirstRow or FirstColumn, use the Long data type.
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Item: .Cells(LastRow, LastColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number LastRow and column number LastColumn.
LastRow and LastColumn are the number of, respectively, the last row and last column in the cell range you work with. If you explicitly declare a variable to represent LastRow or LastColumn, use the Long data type.
- VBA Construct: Worksheet.Cells property and Range.Item property.
Line #3: .AutoFilter Field:=CriteriaField, Criteria1:=Value
- Item: .AutoFilter.
- VBA Construct: Range.AutoFilter method.
- Description: Filter the data within the range you work with using the AutoFilter and according to the parameters specified by items #2 and #3 below.
- VBA Construct: Range.AutoFilter method.
- Item: Field:=CriteriaField.
- VBA Construct: Field parameter of Range.AutoFilter method.
- Description: Specifies the field on which you want to base the filter. The leftmost field of the range you work with is Field 1. The rightmost field is the number of fields in the cell range you work with.
If you explicitly declare a variable to represent CriteriaField, use the Long data type.
- VBA Construct: Field parameter of Range.AutoFilter method.
- Item: Criteria1:=Value.
- VBA Construct: Criteria1 parameter of Range.AutoFilter method.
- Description: Specifies the filtering criteria. If you explicitly declare a variable to represent Value, ensure that the data type you use can handle the value you use as criteria.
- VBA Construct: Criteria1 parameter of Range.AutoFilter method.
Line #4: On Error Resume Next
- Item: On Error Resume Next.
- VBA Construct: On Error Resume Next statement.
- Description: Specifies that, when a run-time error occurs, control goes to the statement following the statement where the error occurs.
The error-handler in this line #4 is necessary because, if the field you filter by (line #3 above) doesn't contain cells with the value you use as criteria, line #5 below generates a run-time error.
- VBA Construct: On Error Resume Next statement.
Line #5: .Offset(RowOffset:=1).Resize(RowSize:=(.Rows.Count – 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
- Item: Offset.
- VBA Construct: Range.Offset property.
- Description: Returns a Range object a number of rows above or below the cell range it works with, as returned by line #2 above.
- VBA Construct: Range.Offset property.
- Item: RowOffset:=1.
- VBA Construct: RowOffset parameter of Range.Offset property.
- Description: Specifies that the cell range returned by Range.Offset (item #1 above) is 1 row below the range specified in line #2 above.
Line #2 above specifies the cell range you work with. Therefore, the Range object that Range.Offset returns has the same size but is 1 row below the cell range you work with. This results in the following:
- The headers of the cell range you work with are excluded from the Range object.
- The first empty row below the last row with data (LastRow in line #2 above) is included. This extra line is handled by item #7 below.
- The headers of the cell range you work with are excluded from the Range object.
- VBA Construct: RowOffset parameter of Range.Offset property.
- Item: Resize.
- VBA Construct: Range.Resize property.
- Description: Resizes the cell range returned by items #1 and #2 above.
- VBA Construct: Range.Resize property.
- Item: RowSize.
- VBA Construct: RowSize parameter of Range.Resize property.
- Description: Specifies the number of rows in the new cell range returned by Range.Resize (item #3 above).
- VBA Construct: RowSize parameter of Range.Resize property.
- Item: Rows.
- VBA Construct: Range.Rows property.
- Description: Returns a Range object representing the rows in the cell range it works with, as returned by line #2 above.
- VBA Construct: Range.Rows property.
- Item: Count.
- VBA Construct: Range.Count property.
- Description: Returns the number of rows within the Range object returned by item #5 above.
- VBA Construct: Range.Count property.
- Item: Resize(RowSize:=(.Rows.Count – 1)).
- VBA Construct: Range.Resize property.
- Description: Resizes the cell range returned by items #1 and #2 above to reduce it by one row. The number of rows in the new range is obtained by subtracting 1 from the number of rows returned by line #2, as counted by items #5 and #6 above.
This results in a cell range that excludes the first empty row below the last row with data that the Range.Offset property (items #1 and #2 above) included.
- VBA Construct: Range.Resize property.
- Item: SpecialCells(xlCellTypeVisible).
- VBA Construct: Range.SpecialCells method and Type parameter of Range.SpecialCells method.
- Description: Returns a Range object representing all visible cells within the cell range you work with, excluding the headers (as required by item #2 above).
Since line #3 above filters the data according to the criteria you specify, the visible cells returned by Range.SpecialCells are those containing the value you're looking for in the column (field) you specify.
- VBA Construct: Range.SpecialCells method and Type parameter of Range.SpecialCells method.
- Item: EntireRow.
- VBA Construct: Range.EntireRow property.
- Description: Returns a Range object representing the entire rows containing the Range object returned by item #8 above.
- VBA Construct: Range.EntireRow property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #10 above.
- VBA Construct: Range.Delete method.
Line #7: .AutoFilterMode = False
- Item: .AutoFilterMode = False.
- VBA Construct: Worksheet.AutoFilterMode property.
- Description: Specifies that the AutoFilter drop-down arrows aren't displayed on the worksheet.
- VBA Construct: Worksheet.AutoFilterMode property.
Macro Example
The following macro deletes all rows that meet the following conditions:
- Are between:
- Rows number (myFirstRow + 1) and myLastRow.
- Columns number myFirstColumn and myLastColumn.
- Rows number (myFirstRow + 1) and myLastRow.
- Contain the value myValue in field number myCriteriaField.
In this example:
- myFirstRow is set to 5.
- myFirstColumn is set to 2.
- myCriteriaField is set to 1.
- myValue is set to 5.
- myLastRow is set to the number of the last row with data in the worksheet named “Delete row based on value”. The constructs used by the statement that finds the last row with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Row property.
- myLastColumn is set to the number of the last column with data in the same worksheet. The constructs used by the statement that finds the last column with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Column property.
Sub deleteRowBasedOnValue() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Dim myFirstRow As Long Dim myLastRow As Long Dim myFirstColumn As Long Dim myLastColumn As Long Dim myCriteriaField As Long Dim myValue As Double Dim myWorksheet As Worksheet myFirstRow = 5 myFirstColumn = 2 myCriteriaField = 1 myValue = 5 Set myWorksheet = Worksheets("Delete row based on value") With myWorksheet With .Cells myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column End With With .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) .AutoFilter Field:=myCriteriaField, Criteria1:=myValue On Error Resume Next .Offset(RowOffset:=1).Resize(RowSize:=(.Rows.Count - 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With .AutoFilterMode = False End With End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, VBA deletes the rows containing myValue (5) in myCriteriaField (1).
#11: Delete Row Based on Date
VBA Code to Delete Row Based on Date
To delete rows based on the date in a specific cell using VBA, use a macro with the following statement structure:
With Worksheet For Counter = LastRow To FirstRow Step -1 With .Cells(Counter, CriteriaColumn) If .Value = Date Then If Not RowsWithDate Is Nothing Then Set RowsWithDate = Union(RowsWithDate, .Cells) Else Set RowsWithDate = .Cells End If End If End With Next Counter End With If Not RowsWithDate Is Nothing Then RowsWithDate.EntireRow.Delete
Process Followed by VBA Code
VBA Statement Explanation
Lines #1 and #13: With Worksheet | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: Statements within the With… End With statement (lines #2 through #12 below) are executed on the worksheet returned by item #2 below.
- VBA Construct: With… End With statement.
- Item: Worksheet.
- VBA Construct: Workbook.Worksheets property.
- Description: Returns a Worksheet object representing the worksheet you work with.
- VBA Construct: Workbook.Worksheets property.
Lines #2 and #12: For Counter = LastRow To FirstRow Step -1 | Next Counter
- Item: For… Next Counter.
- VBA Construct: For… Next statement.
- Description: Repeats the statements within the loop (lines #3 through #11 below) for each row between (and including FirstRow (item #4 below) and LastRow (item #3 below).
- VBA Construct: For… Next statement.
- Item: Counter.
- VBA Construct: Counter of For… Next statement.
- Description: Loop counter. If you explicitly declare a variable to represent the loop counter, use the Long data type.
- VBA Construct: Counter of For… Next statement.
- Item: LastRow.
- VBA Construct: Counter Start of For… Next statement.
- Description: Number of the last row (further down the worksheet) you want the macro to consider when identifying blank rows. The number of the last row is also the initial value of Counter (item #2 above).
If you explicitly declare a variable to represent the number of the last row to consider, use the Long data type.
- VBA Construct: Counter Start of For… Next statement.
- Item: FirstRow.
- VBA Construct: Counter End of For… Next statement.
- Description: Number of the first row (closer to the top of the worksheet) you want the macro to consider when identifying blank rows. The number of the first row is also the final value of Counter (item (#2 above).
If you explicitly declare a variable to represent the number of the first row to consider, use the Long data type.
- VBA Construct: Counter End of For… Next statement.
- Item: Step -1.
- VBA Construct: Step of For… Next statement.
- Description: Amount by which Counter (item #2 above) changes every time a loop iteration occurs.
In this scenario, you loop backwards: from LastRow (item #3 above) to FirstRow (item #4 above). Therefore, step is -1.
- VBA Construct: Step of For… Next statement.
Lines #3 and #11: With .Cells(Counter, CriteriaColumn) | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: Statements within the With… End With statement (lines #4 through #10 below) are executed on the cell returned by item #2 below.
- VBA Construct: With… End With statement.
- Item: .Cells(Counter, CriteriaColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number Counter and column number CriteriaColumn.
At any given time, the value of the loop counter (Counter) is the same as that of the row through which the macro is currently looping. CriteriaColumn is the number of the column containing the cells with dates you consider.
- VBA Construct: Worksheet.Cells property and Range.Item property.
Line #4: If .Value = Date Then
- Item: If… Then.
- VBA Construct: Opening line of If… Then… Else statement.
- Description: Conditionally executes the statements within the If… Then block (lines #5 through #9 below) if the condition specified by item #3 below is met.
- VBA Construct: Opening line of If… Then… Else statement.
- Item: .Value.
- VBA Construct: Range.Value property.
- Description: Returns the value of the cell represented by the Range object returned by line #3 above (.Cells(Counter, CriteriaColumn)). This is the value of the cell at the intersection of the row through which the macro is currently looping and the column containing the cells with dates you consider.
- VBA Construct: Range.Value property.
- Item: .Value = Date.
- VBA Construct: Condition of If… Then… Else statement.
- Description: This condition is a numeric expression that evaluates to True or False, as follows:
- True: When the value of the cell at the intersection of the row through which the macro is currently looping and the column containing the cells with dates you consider is equal to the date you specify (Date).
- False: When the value of the cell at the intersection of the row through which the macro is currently looping and the column containing the cells with dates you consider isn't equal to the date you specify (Date).
If you explicitly declare a variable to represent Date, ensure that the data type you use can handle the value you use as criteria. Consider, for example, using the Date data type.
When specifying the date you use as criteria, ensure that you specify the date as a value as required by VBA. For these purposes, you can use VBA constructs such as the DateValue or DateSerial Functions. - True: When the value of the cell at the intersection of the row through which the macro is currently looping and the column containing the cells with dates you consider is equal to the date you specify (Date).
- VBA Construct: Condition of If… Then… Else statement.
Line #5: If Not RowsWithDate Is Nothing Then
- Item: If… Then.
- VBA Construct: Opening line of If… Then… Else statement.
- Description: Conditionally executes the statement within the If… Then… Else block (line #6 below) if the condition specified by item #6 below is met.
- VBA Construct: Opening line of If… Then… Else statement.
- Item: Not.
- VBA Construct: Not operator.
- Description: Carries out a logical negation on item #3 below. In other words, if item #3 returns:
- True, the result is False.
- False, the result is True.
- True, the result is False.
- VBA Construct: Not operator.
- Item: RowsWithDate.
- VBA Construct: Object (Range) variable.
- Description: Holds a Range object representing the cells in the column you specify (CriteriaColumn in line #3 above) containing the date you use as criteria.
- VBA Construct: Object (Range) variable.
- Item: Is.
- VBA Construct: Is Operator.
- Description: Compares 2 object reference variables: (i) Not RowsWithDate (items #2 and #3 above) vs. (ii) Nothing (item #5 below).
If both object references refer to the same object, the Is operator returns True. If they refer to different objects, Is returns False.
- VBA Construct: Is Operator.
- Item: Nothing.
- Description: The default value for a data type. In the case of an object variable (such as RowsWithDate), a null reference.
- Description: The default value for a data type. In the case of an object variable (such as RowsWithDate), a null reference.
- Item: Not RowsWithDate Is Nothing.
- VBA Construct: Condition of If… Then… Else statement.
- Description: The condition is an expression that evaluates to True or False, as follows:
- True: When “Not RowsWithDate” (items #2 and #3 above) refers to the same object as Nothing (item #5 above). This happens when RowsWithDate is “something”.
Since RowsWithDate holds a Range object representing the cells with the criteria date found by the macro in a specific column (CriteriaColumn in line #3 above), RowsWithDate is something after the macro finds the first such cell. - False: When “Not RowsWithDate” refers to a different object from Nothing. This happens when RowsWithDate itself is Nothing. This occurs prior to the macro finding the first cell with the criteria date. This is because RowsWithDate isn't assigned to anything prior to that moment.
- True: When “Not RowsWithDate” (items #2 and #3 above) refers to the same object as Nothing (item #5 above). This happens when RowsWithDate is “something”.
- VBA Construct: Condition of If… Then… Else statement.
Line #6: Set RowsWithDate = Union(RowsWithDate, .Cells)
- Item: Set… =.
- VBA Construct: Set statement.
- Description: Assigns the object reference returned by item #6 below to RowsWithDate (item #2 below).
- VBA Construct: Set statement.
- Item: RowsWithDate.
- VBA Construct: Object (Range) variable of Set statement.
- Description:
- Holds a Range object representing the cells in the column you specify (CriteriaColumn in line #3 above) containing the date you use as criteria.
- RowsWithDate is included twice in the statement. In the first mention (Set RowsWithDate), RowsWithDate is the object variable to which an object reference is assigned.
- Holds a Range object representing the cells in the column you specify (CriteriaColumn in line #3 above) containing the date you use as criteria.
- VBA Construct: Object (Range) variable of Set statement.
- Item: Union.
- VBA Construct: Application.Union method.
- Description: Returns a Range object representing the union of the Range objects returned by items #4 and #5 below.
- VBA Construct: Application.Union method.
- Item: RowsWithDate.
- VBA Construct: Object (Range) variable.
- Description:
- Holds a Range object representing the cells in the column you specify (CriteriaColumn in line #3 above) containing the date you use as criteria.
- RowsWithDate is included twice in the statement. In the second mention (Union(RowsWithDate, .Cells), RowsWithDate is one of the parameters of the Application.Union method.
- Holds a Range object representing the cells in the column you specify (CriteriaColumn in line #3 above) containing the date you use as criteria.
- VBA Construct: Object (Range) variable.
- Item: .Cells.
- VBA Construct: Range.Cells property.
- Description: Returns a Range object representing the cell represented by the Range object returned by line #3 above (.Cells(Counter, CriteriaColumn)). This is the cell at the intersection of the row through which the macro is currently looping and the column containing the cells with dates you consider.
- VBA Construct: Range.Cells property.
- Item: Union(RowsWithDate, .Cells).
- VBA Construct: Object expression of Set statement.
- Description: Returns the new Range object reference assigned to the RowsWithDate object variable (item #2 above). This is the union of the following 2 Range objects:
- Prior to the Set statement, RowsWithDate represents cells in the column you specify containing the date you use as criteria found by the macro prior to the row through which it's currently looping.
- “.Cells” represents the cell at the intersection of the row through which the macro is currently looping and the column containing the cells with dates you consider.
Graphically, this looks as follows:
In other words, any cell containing the criteria date the macro finds is “added” to RowsWithDate. - Prior to the Set statement, RowsWithDate represents cells in the column you specify containing the date you use as criteria found by the macro prior to the row through which it's currently looping.
- VBA Construct: Object expression of Set statement.
Line #7: Else
- Item: Else.
- VBA Construct: Else clause of If… Then… Else statement.
- Description: The statement following the Else clause (line #8 below) is executed if the condition tested in the opening line of the If… Then… Else statement (line #5 above) isn't met and returns False.
- VBA Construct: Else clause of If… Then… Else statement.
Line #8: Set RowsWithDate = .Cells
- Item: Set… =.
- VBA Construct: Set statement.
- Description: Assigns the object reference returned by item #3 below to RowsWithDate (item #2 below).
- VBA Construct: Set statement.
- Item: RowsWithDate.
- VBA Construct: Object (Range) variable of Set statement.
- Description: Holds a Range object representing the cells in the column you specify (CriteriaColumn in line #3 above) containing the date you use as criteria.
- VBA Construct: Object (Range) variable of Set statement.
- Item: .Cells.
- VBA Construct: Range.Cells property.
- Description: Returns a Range object representing the cell represented by the Range object returned by line #3 above (.Cells(Counter, CriteriaColumn)). This is the cell at the intersection of the row through which the macro is currently looping and the column containing the cells with dates you consider.
- VBA Construct: Range.Cells property.
Lines #9 and #10: End If | End If
- Item: End If.
- VBA Construct: Closing lines of If… Then… Else statements.
- Description: Ends the If… Then… Else statements that began in lines #4 and #5 above.
- VBA Construct: Closing lines of If… Then… Else statements.
Line #14: If Not RowsWithDate Is Nothing Then RowsWithDate.EntireRow.Delete
- Item: If… Then.
- VBA Construct: If… Then… Else statement.
- Description: Conditionally executes the statement within at the end of the line (items #7 through #9 below) if the condition specified by item #6 below is met.
- VBA Construct: If… Then… Else statement.
- Item: Not.
- VBA Construct: Not operator.
- Description: Carries out a logical negation on item #3 below. In other words, if item #3 returns:
- True, the result is False.
- False, the result is True.
- True, the result is False.
- VBA Construct: Not operator.
- Item: RowsWithDate.
- VBA Construct: Object (Range) variable.
- Description: Holds a Range object representing the cells in the column you specify (CriteriaColumn in line #3 above) containing the date you use as criteria.
- VBA Construct: Object (Range) variable.
- Item: Is.
- VBA Construct: Is Operator.
- Description: Compares 2 object reference variables: (i) Not RowsWithDate (items #2 and #3 above) vs. (ii) Nothing (item #5 below).
If both object references refer to the same object, the Is operator returns True. If they refer to different objects, Is returns False.
- VBA Construct: Is Operator.
- Item: Nothing.
- Description: The default value for a data type. In the case of an object variable (such as RowsWithDate), a null reference.
- Description: The default value for a data type. In the case of an object variable (such as RowsWithDate), a null reference.
- Item: Not RowsWithDate Is Nothing.
- VBA Construct: Condition of If… Then… Else statement.
- Description: The condition is an expression that evaluates to True or False, as follows:
- True: When “Not RowsWithDate” (items #2 and #3 above) refers to the same object as Nothing (item #5 above). This happens when RowsWithDate is “something”.
Since RowsWithDate holds a Range object representing the cells with the criteria date found by the macro in a specific column (CriteriaColumn in line #3 above), RowsWithDate is something if the macro finds at least one such cell. - False: When “Not RowsWithDate” refers to a different object from Nothing. This happens when RowsWithDate itself is Nothing. This, in turn, occurs when the macro founds no cells with the criteria date within the specified column.
- True: When “Not RowsWithDate” (items #2 and #3 above) refers to the same object as Nothing (item #5 above). This happens when RowsWithDate is “something”.
- VBA Construct: Condition of If… Then… Else statement.
- Item: RowsWithDate.
- VBA Construct: Object (Range) variable.
- Description: Holds a Range object representing the cells in the column you specify (CriteriaColumn in line #3 above) containing the date you use as criteria.
- VBA Construct: Object (Range) variable.
- Item: EntireRow.
- VBA Construct: Range.EntireRow property.
- Description: Returns a Range object representing the entire row containing the cell range returned by item #7 above.
- VBA Construct: Range.EntireRow property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #8 above.
- VBA Construct: Range.Delete method.
Macro Example
The following macro deletes all rows that meet the following conditions:
- Are between rows number myFirstRow and myLastRow.
- Contain the date myDate in column number myCriteriaColumn.
In this example:
- myFirstRow is set to 5.
- myDate is set to the serial number representing June 15, 2017. For purposes of obtaining the appropriate serial number, I use the DateValue Function.
- myCriteriaColumn is set to 2.
- myLastRow is set to the number of the last row with data in the worksheet named “Delete row based on date”. The constructs used by the statement that finds the last row with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Row property.
Sub deleteRowBasedOnDate() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Dim myFirstRow As Long Dim myLastRow As Long Dim myCriteriaColumn As Long Dim myDate As Date Dim myWorksheet As Worksheet Dim iCounter As Long Dim myRowsWithDate As Range myFirstRow = 6 myCriteriaColumn = 2 myDate = DateValue("June 15, 2017") Set myWorksheet = Worksheets("Delete row based on date") With myWorksheet myLastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For iCounter = myLastRow To myFirstRow Step -1 With .Cells(iCounter, myCriteriaColumn) If .Value = myDate Then If Not myRowsWithDate Is Nothing Then Set myRowsWithDate = Union(myRowsWithDate, .Cells) Else Set myRowsWithDate = .Cells End If End If End With Next iCounter End With If Not myRowsWithDate Is Nothing Then myRowsWithDate.EntireRow.Delete End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, VBA deletes the rows containing myDate (June 15, 2017) in myCriteriaColumn (2).
#12: Delete Row Based on String Criteria
VBA Code to Delete Row Based on String Criteria
To delete rows based on the string in a specific cell using VBA, use a macro with the following statement structure:
With Worksheet With .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn)) .AutoFilter Field:=CriteriaField, Criteria1:=String On Error Resume Next .Offset(RowOffset:=1).Resize(RowSize:=(.Rows.Count - 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With .AutoFilterMode = False End With
Process Followed by VBA Code
VBA Statement Explanation
Lines #1 and #8: With Worksheet | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: Statements within the With… End With statement (lines #2 through #7 below) are executed on the worksheet returned by item #2 below.
- VBA Construct: With… End With statement.
- Item: Worksheet.
- VBA Construct: Workbook.Worksheets property.
- Description: Returns a Worksheet object representing the worksheet you work with.
- VBA Construct: Workbook.Worksheets property.
Lines #2 and #6: With .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn)) | End With
- Item: With… End With.
- VBA Construct: With… End With statement.
- Description: Statements within the With… End With statement (lines #3 through #6 below) are executed on the range object returned by items #2 through #4 below.
- VBA Construct: With… End With statement.
- Item: .Range.
- VBA Construct: Worksheet.Range property.
- Description: Returns a Range object representing a cell range specified as follows:
- Upper-left corner cell: Range object returned by item #3 below.
- Lower-right corner cell: Range object returned by item #4 below.
- Upper-left corner cell: Range object returned by item #3 below.
- VBA Construct: Worksheet.Range property.
- Item: .Cells(FirstRow, FirstColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number FirstRow and column number FirstColumn.
FirstRow and FirstColumn are the number of, respectively, the first row and first column in the cell range you work with. If you explicitly declare a variable to represent FirstRow or FirstColumn, use the Long data type.
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Item: .Cells(LastRow, LastColumn).
- VBA Construct: Worksheet.Cells property and Range.Item property.
- Description: Returns a Range object representing the cell at the intersection of row number LastRow and column number LastColumn.
LastRow and LastColumn are the number of, respectively, the last row and last column in the cell range you work with. If you explicitly declare a variable to represent LastRow or LastColumn, use the Long data type.
- VBA Construct: Worksheet.Cells property and Range.Item property.
Line #3: .AutoFilter Field:=CriteriaField, Criteria1:=String
- Item: .AutoFilter.
- VBA Construct: Range.AutoFilter method.
- Description: Filter the data within the range you work with using the AutoFilter and according to the parameters specified by items #2 and #3 below.
- VBA Construct: Range.AutoFilter method.
- Item: Field:=CriteriaField.
- VBA Construct: Field parameter of Range.AutoFilter method.
- Description: Specifies the field on which you want to base the filter. The leftmost field of the range you work with is Field 1. The rightmost field is the number of fields in the cell range you work with.
If you explicitly declare a variable to represent CriteriaField, use the Long data type.
- VBA Construct: Field parameter of Range.AutoFilter method.
- Item: Criteria1:=String.
- VBA Construct: Criteria1 parameter of Range.AutoFilter method.
- Description: Specifies the filtering criteria. If you explicitly declare a variable to represent String, use the String data type.
- VBA Construct: Criteria1 parameter of Range.AutoFilter method.
Line #4: On Error Resume Next
- Item: On Error Resume Next.
- VBA Construct: On Error Resume Next statement.
- Description: Specifies that, when a run-time error occurs, control goes to the statement following the statement where the error occurs.
The error-handler in this line #4 is necessary because, if the field you filter by (line #3 above) doesn't contain cells with the string you use as criteria, line #5 below generates a run-time error.
- VBA Construct: On Error Resume Next statement.
Line #5: .Offset(RowOffset:=1).Resize(RowSize:=(.Rows.Count – 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
- Item: Offset.
- VBA Construct: Range.Offset property.
- Description: Returns a Range object a number of rows above or below the cell range it works with, as returned by line #2 above.
- VBA Construct: Range.Offset property.
- Item: RowOffset:=1.
- VBA Construct: RowOffset parameter of Range.Offset property.
- Description: Specifies that the cell range returned by Range.Offset (item #1 above) is 1 row below the range specified in line #2 above.
Line #2 above specifies the cell range you work with. Therefore, the Range object that Range.Offset returns has the same size but is 1 row below the cell range you work with. This results in the following:
- The headers of the cell range you work with are excluded from the Range object.
- The first empty row below the last row with data (LastRow in line #2 above) is included. This extra line is handled by item #7 below.
- The headers of the cell range you work with are excluded from the Range object.
- VBA Construct: RowOffset parameter of Range.Offset property.
- Item: Resize.
- VBA Construct: Range.Resize property.
- Description: Resizes the cell range returned by items #1 and #2 above.
- VBA Construct: Range.Resize property.
- Item: RowSize.
- VBA Construct: RowSize parameter of Range.Resize property.
- Description: Specifies the number of rows in the new cell range returned by Range.Resize (item #3 above).
- VBA Construct: RowSize parameter of Range.Resize property.
- Item: Rows.
- VBA Construct: Range.Rows property.
- Description: Returns a Range object representing the rows in the cell range it works with, as returned by line #2 above.
- VBA Construct: Range.Rows property.
- Item: Count.
- VBA Construct: Range.Count property.
- Description: Returns the number of rows within the Range object returned by item #5 above.
- VBA Construct: Range.Count property.
- Item: Resize(RowSize:=(.Rows.Count – 1)).
- VBA Construct: Range.Resize property.
- Description: Resizes the cell range returned by items #1 and #2 above to reduce it by one row. The number of rows in the new range is obtained by subtracting 1 from the number of rows returned by line #2, as counted by items #5 and #6 above.
This results in a cell range that excludes the first empty row below the last row with data that the Range.Offset property (items #1 and #2 above) included.
- VBA Construct: Range.Resize property.
- Item: SpecialCells(xlCellTypeVisible).
- VBA Construct: Range.SpecialCells method and Type parameter of Range.SpecialCells method.
- Description: Returns a Range object representing all visible cells within the cell range you work with, excluding the headers (as required by item #2 above).
Since line #3 above filters the data according to the criteria you specify, the visible cells returned by Range.SpecialCells are those containing the string you're looking for in the column (field) you specify.
- VBA Construct: Range.SpecialCells method and Type parameter of Range.SpecialCells method.
- Item: EntireRow.
- VBA Construct: Range.EntireRow property.
- Description: Returns a Range object representing the entire rows containing the Range object returned by item #8 above.
- VBA Construct: Range.EntireRow property.
- Item: Delete.
- VBA Construct: Range.Delete method.
- Description: Deletes the Range object returned by item #10 above.
- VBA Construct: Range.Delete method.
Line #7: .AutoFilterMode = False
- Item: .AutoFilterMode = False.
- VBA Construct: Worksheet.AutoFilterMode property.
- Description: Specifies that the AutoFilter drop-down arrows aren't displayed on the worksheet.
- VBA Construct: Worksheet.AutoFilterMode property.
Macro Example
The following macro deletes all rows that meet the following conditions:
- Are between:
- Rows number (myFirstRow + 1) and myLastRow.
- Columns number myFirstColumn and myLastColumn.
- Rows number (myFirstRow + 1) and myLastRow.
- Contain the string myString in field number myCriteriaField.
In this example:
- myFirstRow is set to 5.
- myFirstColumn is set to 2.
- myCriteriaField is set to 1.
- myString is set to “*to delete*”.
The asterisks at the beginning and end of the string act as wildcards representing any number of characters. Therefore, myString includes any strings that contain “to delete”, regardless of the text before or after it.
For example, in the example below, I use this macro to delete rows where the cell in the first column contains the string “Rows to delete now”. “to delete” is between the strings “Rows ” and ” now”, both of which are covered by the asterisk wildcard. - myLastRow is set to the number of the last row with data in the worksheet named “Delete row based on string”. The constructs used by the statement that finds the last row with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Row property.
- myLastColumn is set to the number of the last column with data in the same worksheet. The constructs used by the statement that finds the last column with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Column property.
Sub deleteRowBasedOnString() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excel-vba-delete-row/ Dim myFirstRow As Long Dim myLastRow As Long Dim myFirstColumn As Long Dim myLastColumn As Long Dim myCriteriaField As Long Dim myString As String Dim myWorksheet As Worksheet myFirstRow = 5 myFirstColumn = 2 myCriteriaField = 1 myString = "*to delete*" Set myWorksheet = Worksheets("Delete row based on string") With myWorksheet With .Cells myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column End With With .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) .AutoFilter Field:=myCriteriaField, Criteria1:=myString On Error Resume Next .Offset(RowOffset:=1).Resize(RowSize:=(.Rows.Count - 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With .AutoFilterMode = False End With End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, VBA deletes the rows containing myString (“*to delete*”) in myCriteriaField (1).
References to VBA Constructs Used in this VBA Tutorial
Use the following links to visit the appropriate webpage within the Microsoft Office Dev Center:
- Identify the worksheet you work with:
- Find last row and last column with data in a worksheet and count number of rows in a cell range:
- Return Range objects:
- Return Range objects representing rows:
- Loop through rows:
- Specify criteria for row deletion:
- Test if (i) rows meet criteria for deletion, or (ii) the macro has found rows or cells meeting the criteria for deletion:
- Delete rows.
- Work with variables:
- Simplify object references:
- Handle errors:
- Remove AutoFilter drop-down arrows: