VBA Delete Row – Excel Examples Macros Codes

Excel VBA code to Delete Rows examples Macros will help us to delete rows from excel worksheet in various criteria. We can use VBA delete row macros to delete entire row, blank cells or empty rows, move up or shift up, delete row if cell contains a string, date or value, from a sheet, table and range. We will use Rows().Delete method to delete the rows in of Microsoft Excel 2003, Excel 2007, Excel 2010, and Excel 2013. Excel VBA Macro code for deleting rows macro will help you to know how to delete rows in excel.

VBA code to Delete Rows in Excel

Following are the VBA Syntax and sample VBA macro commands to Delete Rows from worksheet using Excel VBA. We will see the various examples to delete the rows in different situations.

VBA code Example Macros to Delete Entire Row

The following Excel VBA Example macro code is to delete entire row from the worksheet. This VBA macro will delete the first row in the worksheet.

Sub sbVBS_To_Delete_EntireRow ()
    Rows(1).EntireRow.Delete
End Sub

If you execute this macro, this will delete the first row from the worksheet as we mentioned 1 in the code. Similarly, we can use any number to delete the specific row using VBA.

Rows(

[Row Number]).EntireRow.Delete


Here Row Number is your row number to delete. And EntireRow.Delete method will delete the Entire row from the Excel spreadsheet.

Delete Entire Row using VBA – Excel Macro Example Code

VBA Delete Row:Example Macro code to Delete Active Row

The following Excel VBA Example macro code is to delete active row from the worksheet. This VBA macro will delete the row of the currently active cell in the worksheet.

Sub sbVBS_To_Delete_Active_Rows()
Rows(ActiveCell.Row).Delete
End Sub 
 

If you execute this macro, this will delete the active row from the worksheet as we mentioned ActiveCell.Row in the code. ActiveCell.Row returns the row number of the currently active cell.

Rows(ActiveCell.Row).EntireRow.Delete


Here ActiveCell.Row return the row number of the active cell. And EntireRow.Delete method will delete the rows from the Excel spreadsheet.
Delete Active row using VBA

VBA code Example Macros to delete blank rows in a Range

The following Excel VBA Example macro code is to delete blank rows in a range from the worksheet. This VBA macro will delete the blank rows in a range in the worksheet.

Sub sbVBS_To_Delete_Blank_Rows_In_Range()
Dim iCntr
Dim rng As Range
Set rng = Range("A10:D20")

    For iCntr = rng.Row + rng.Rows.Count - 1 To rng.Row Step -1
        If Application.WorksheetFunction.CountA(Rows(iCntr)) = 0 Then Rows(iCntr).EntireRow.Delete
    Next

End Sub 

If you execute this macro, this will delete the blank rows in a range. We are checking the blank rows using if condition and CountA function to determine the row is blank. And then we are using Delete method of rows to delete the rows in a Excel range.

If Application.WorksheetFunction.CountA(Rows(RowNumber)) = 0 Then Rows(RowNumber).EntireRow.Delete


Here Application.WorksheetFunction.CountA will check if the row is blank. And the Rows.delete method will delete the row if row is blank.
Delete blank rows in a range using VBA

VBA code Example Macros to delete blank rows in a Table

The following Excel VBA Example macro code is to delete blank rows in a table from the worksheet. This VBA macro will delete the blank rows in a table in the worksheet.

Sub sbVBS_To_Delete_Blank_Rows_In_Table()
Dim iCntr As Long
Dim rng As Range

Set rng = ActiveSheet.ListObjects("Table1").Range

For iCntr = rng.Row + rng.Rows.Count - 1 To rng.Row Step -1
        If Application.WorksheetFunction.CountA(Rows(iCntr)) = 0 Then Rows(iCntr).EntireRow.Delete
    Next
End Sub 

If you execute this macro, this will delete the blank rows in table. We are checking the blank rows using if condition and CountA function to determine the row is blank in the table. And then we are using Delete method of rows to delete the rows in a table.


If Application.WorksheetFunction.CountA(Rows(RowNumber)) = 0 Then Rows(RowNumber).EntireRow.Delete


Here Application.WorksheetFunction.CountA will check if the row is blank. And the Rows.delete method will delete the row if row is blank.
Delete blank rows in a table using VBA

VBA code Example Macros to delete hidden rows

The following Excel VBA Example macro code is to hidden rows from the worksheet. This VBA macro will delete the hidden rows in a worksheet.

Sub sbVBS_To_Delete_Hidden_Rows()
Dim lastRow
lastRow = 100
    For iCntr = lastRow To 1 Step -1
        If Rows(iCntr).Hidden = True Then Rows(iCntr).EntireRow.Delete
    Next
End Sub 

If you execute this macro, this will delete the hidden rows in a worksheet. We are checking the rows using Hidden property of a row and then deleting if the row is hidden.

If Rows(RowNumber).Hidden = True Then Rows(RowNumber).EntireRow.Delete


Here Rows(RowNumber).Hidden is to check if the row is hidden. And EntireRow.Delete method will delete the rows from the Excel spreadsheet if they are hidden.
Delete Hidden Rows using VBA

VBA code Example Macros to delete multiple rows

The following Excel VBA Example macro code is to multiple rows from the worksheet. This VBA macro will delete the multiple rows at one shot from a worksheet.

Sub sbVBS_To_Delete_Multiple_Rows ()
    Rows(“1:3”).EntireRow.Delete
End Sub

If you execute this macro, this will delete the multiple rows from a worksheet. We can specify any number of rows (starting and ending row number) based on the system memory.


Rows(“[Row Numbers]”).EntireRow.Delete


Here Row Numbers are your row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.
Delete Multiple Rows using VBA – Excel Macro Example Code

VBA code Example Macros to delete rows based on Cell Color

Following is the VBA syntax and sample VBA code to delete rows based on Cell fill Color from worksheet using VBA. We are using the Delete method of the Rows object of worksheet to delete rows.

Sub sbDelete_Rows_Based_On_Cell_Color()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1).Interior.ColorIndex = 3 Then ‘3=Red
        Rows(iCntr).Delete
    End If
Next
End Sub
The above VBA code is to delete rows based on Cell Color from the excel worksheet. This code will delete the rows (1 to 20) if it satisfy the color condition if cell background is red (Interior.ColorIndex=3).

<code>
If <color criteria> Then Rows(“[Row Numbers]”).EntireRow.Delete 
</code>

Here <color criteria> is the color condition which you want to check to delete rows. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.
<a href="https://analysistabs.com/vba/delete-rows-based-on-cell-color-excel-macro-example-code/" class="more-link" title="Delete rows based on Cell Color using VBA">Delete rows based on Cell Color using VBA</a>

<H3>VBA code Example Macros to delete rows based on Cell font Color </H3>
Following is the VBA syntax and sample VBA code to delete rows based on Cell font Color from worksheet using VBA.  We are using the Delete method of the Rows object of worksheet to delete rows.
 
Sub sbDelete_Rows_Based_On_Cell_Color()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1).Font.ColorIndex = 3 Then ‘3=Red
        Rows(iCntr).Delete
    End If
Next
End Sub

The above VBA code is to delete rows based on Cell font Color from the excel worksheet. This code will delete the rows (1 to 20) if it satisfy the color condition if cell font color is red (Font.ColorIndex = 3).


If  Then Rows(“[Row Numbers]”).EntireRow.Delete


Here  is the color condition which you want to check to delete rows. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete rows based on Cell font Color using VBA
VBA code Example Macros to delete rows based on cell value 


Following is the VBA syntax and sample VBA code to delete rows based on cell value from worksheet using VBA.  We are checking the cell value and using the Delete method of the Rows object of worksheet to delete rows.

Sub sbDelete_Rows_Based_On_Criteria()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1) = 10 Then
        Rows(iCntr).Delete
    End If
Next
End Sub



If  Then Rows(“[Row Numbers]”).EntireRow.Delete


Here cell value criteria is the condition which you want to check the cells to delete rows. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete rows based on cell value using Excel VBA
VBA code Example Macros to delete rows based on Criteria


Following is the VBA syntax and sample VBA code to delete rows based on criteria from worksheet using VBA.  We are checking the cell for criteria and using the Delete method of the Rows object of worksheet to delete rows.

Sub sbDelete_Rows_Based_On_Criteria()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1) = 1 Then
        Rows(iCntr).Delete
    End If
Next
End Sub



If  Then Rows(“[Row Numbers]”).EntireRow.Delete


Here criteria is the condition which you want to check to delete rows. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete rows based on criteria using VBA
VBA code Example Macros to delete rows based on Date


Following is the VBA syntax and sample VBA code to delete rows based on Date from worksheet using VBA.  We are checking the cell value for a specific date and using the Delete method of the Rows object of worksheet to delete rows.

Sub sbDelete_Rows_Based_On_Date()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Format(Cells(iCntr, 1), "dd-mm-yyyy”) = Format(Now(), "dd-mm-yyyy") Then
        Rows(iCntr).Delete
    End If
Next
End Sub



If  Then Rows(“[Row Numbers]”).EntireRow.Delete


Here  is the date condition which you want to check to delete rows. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete rows based on Date using VBA
VBA code Example Macros to delete rows based on multiple criteria 


Here is the Example VBA syntax and Example VBA Macro to delete rows from excel worksheets based o multiple criteria. This will help you to know how to delete specific rows based on multiple conditions from Excel workbook using VBA.

Sub sbDelete_Rows_Based_On_Multiple_Criteria()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1) = 1 or trim(Cells(iCntr, 1)) = “” Then
        Rows(iCntr).Delete
    End If
Next
End Sub



If  Then Rows(“[Row Numbers]”).EntireRow.Delete


Here  is the conditions which you want to check to delete rows. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete rows based on multiple criteria using VBA
VBA code Example Macros to delete rows if cell contains Error value 


Here is the Example VBA syntax and Example VBA Macro to delete rows from excel worksheets if cell contains Error value. This will help you to know how to delete specific rows if cell contains Error value from Excel workbook using VBA.

Sub sbDelete_Rows_IF_Cell_Contains_Error()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If IsError(Cells(iCntr, 1)) Then
        Rows(iCntr).Delete
    End If
Next
End Sub



If  Then Rows(“[Row Numbers]”).EntireRow.Delete


Here  to check if the cell contains any error. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete rows if cell contains Error value using VBA
VBA code Example Macros to delete rows if cell contains Number value


Here is the Example VBA syntax and Example VBA Macro to delete rows from excel worksheets if cell contains Number value. This will help you to know how to delete specific rows if cell contains Number value from Excel workbook using VBA.

Sub sbDelete_Rows_IF_Cell_Contains_Error()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If isNumeric(Cells(iCntr, 1)) Then
        Rows(iCntr).Delete
    End If
Next
End Sub



If  Then Rows(“[Row Numbers]”).EntireRow.Delete


Here  to check if the cell contains any error. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete rows if cell contains Number value using VBA
VBA code Example Macros to delete rows if cell contains string value 


Here is the Example VBA syntax and Example VBA Macro to delete rows from excel worksheets if cell contains string value. This will help you to know how to delete specific rows if cell contains string value from Excel workbook using VBA.

Sub sbDelete_Rows_IF_Cell_Cntains_String_Text_Value()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1) = “Your String” Then ‘ You can change this text
        Rows(iCntr).Delete
    End If
Next
End Sub



If  Then Rows(“[Row Numbers]”).EntireRow.Delete


Here  to check if the cell contains a specific text. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete rows if cell contains string value using VBA
VBA code Example Macros to delete rows if cell is 0 (equals to zero)


Here is the Example VBA syntax and Example VBA Macro to delete rows from excel worksheets if cell is 0 (equals to zero). This will help you to know how to delete specific rows if cell is 0 (equals to zero) from Excel workbook using VBA.

Sub sbDelete_Rows_IF_Cell_Contains_Error()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1)=0 Then
        Rows(iCntr).Delete
    End If
Next
End Sub



If < (cell value)=0> Then Rows(“[Row Numbers]”).EntireRow.Delete


Here < (cell value)=0> to check if the cell contains 0 (zero). And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete rows if cell is 0 (equals to zero) using VBA
VBA code Example Macros to delete rows if Cell is Empty 


Here is the Example VBA syntax and Example VBA Macro to delete rows from excel worksheets if Cell is Empty/Blanks. This will help you to know how to delete specific rows if Cell is Empty/Blanks from Excel workbook using VBA.

Sub sbDelete_Rows_IF_Cell_Is_Blank()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If trim(Cells(iCntr, 1)) = “” Then
        Rows(iCntr).Delete
    End If
Next
End Sub



If trim() =”” Then Rows(“[Row Numbers]”).EntireRow.Delete


Here trim() =””is to check if the cell is empty. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete row if Cell is Empty/Blank using Excel VBA
VBA code Example Macros to delete rows in range 


Here is the Example VBA syntax and Example VBA Macro to delete rows in range from excel worksheets. This will help you to know how to delete specific rows in a range from Excel workbook using VBA.

Sub sbVBS_To_Delete_Rows_In_Range()
Dim iCntr
Dim rng As Range
Set rng = Range("A10:D20")

    For iCntr = rng.Row + rng.Rows.Count - 1 To rng.Row Step -1
       Rows(iCntr).EntireRow.Delete
    Next

End Sub 



Rows(RowNumber).EntireRow.Delete


Here Rows.delete method will delete the row in the worksheet.

Delete rows in a range using VBA
VBA code Example Macros to delete rows shift up 


Here is the Example VBA syntax and Example VBA Macro to delete rows to shift up cells from excel worksheets. This will help you to know how to delete specific rows shift up from Excel workbook using VBA.

Sub sbDelete_Rows_IF_Cell_Contains_Error()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1)=0 Then
        Range("A" & iCntr).Delete Shift:=xlUp    End If
Next
End Sub



Range("Your Range”).Delete Shift:=xlUp


Here Range("Your Range”) is your range to delete. And Shift:=xlUp tells excel to shift up the cells not to delete entire row.

Delete rows shift up using VBA
VBA code Example Macros to delete rows with specific data values 


Here is the Example VBA syntax and Example VBA Macro to delete rows from excel worksheets with specific data values. This will help you to know how to delete specific rows with specific data values from Excel workbook using VBA.

Sub sbDelete_Rows_With_Specific_Data ()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1) = “Your Data” Then ‘ You can change this text
  ‘If Cells(iCntr, 1) = “22-12-2013” Then ‘ to check specific date
        Rows(iCntr).Delete
    End If
Next
End Sub



If  Then Rows(“[Row Numbers]”).EntireRow.Delete


Here  to check if the cell contains a specific data. And Row Numbers are the row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.
Delete rows with specific value using VBA – Excel VBA Macro Example Code