VBA Delete Row – Excel Examples Macros Codes

Home/VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


Excel VBA Code Example Macro to Delete Row

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

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference
By |April 25th, 2014|VBA|0 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.