How to delete rows in VBA?
- Excel rows can be deleted by using the Delete method in VBA.
- You may specify WorkSheets.Rows.Delete method to remove one or more rows
- Range object can also be used with EntireRow property for removing rows
- Rows can be deleted from active or some other sheets
- See the examples below for removing one or more rows with different objects and sheets
Using Rows object with Delete method for removing one row
First, let us show you how to delete a single row by using the Delete method of Worksheets.Rows for the active worksheet.
Our sample sheet is as shown below:
For that, you may use the Rows.Delete as shown in the example below:
VBA code:
Sub delete_rows() 'Deleting Row 3 Rows(3).Delete End Sub
Result:
You can see, row number 3 from our sample sheet is removed.
Deleting multiple rows example
You may specify a range of rows to be removed from the active sheet while using Rows.Delete method.
This is how you may specify the rows:
See the example below where we will remove rows from 3 to 8. You can see the sample sheet above and the result below:
Code:
Sub delete_rows() 'Deleting Rows from 3 to 8 Rows("3:8").Delete End Sub
Result:
Deleting rows in another sheet
Suppose, we have multiple sheets in the Workbook.
The active sheet is “Sales” and we want to delete row(s) in the “Product Information” sheet. So, how do I delete that?
In that case, you may use the Worksheets object as shown in the example below.
Sample sheets in the workbook:
Code:
Sub delete_rows() 'Deleting Rows in another sheet Worksheets("Product Information").Rows("4:8").Delete End Sub
The above code deleted five rows from “Product Information” sheet while “Sales” sheet was active.
Second way – Using Range object
Now let us look at using the Range object for removing one or more rows in the Worksheet.
This is how you may use the Range’s Delete method:
The example below deletes row number eight from our Worksheet:
Code:
Sub delete_rows() 'Remove row number 8 Rows(8).EntireRow.Delete End Sub
Deleting multiple rows using EntireRow.Delete
For removing multiple rows, you may use the same way as in Rows.Delete method.
The example below deletes rows 3 to 7 from our sheet.
Code:
Sub delete_rows() 'Remove row number 3 to 7 Rows("3:7").EntireRow.Delete End Sub
Result:
If you compare it with the above sample sheet, you can see five rows are deleted.
Removing empty rows in a range example
To understand the removing empty rows solution below, consider the following sheet:
There you can see empty rows between the rows with data. The code below removes all empty rows in the range of A1 to D40 cells:
Sub delete_rows() Dim cell As Range Dim SheetRange As Range 'Setting our range in the sheet where you want to remove empty rows Set SheetRange = Range("A2:D40") For Each cell In SheetRange If Application.WorksheetFunction.CountA(cell.EntireRow) = 0 Then cell.EntireRow.Delete End If Next cell End Sub
Result:
- A range of cells is created
- For…Each loop is used and we iterated through each row in the range
- By using CountA function of the Application.WorksheetFunction, we checked the entire row.
- If the row was empty, cell.EntireRow.Delete method was used to remove that row.
Remove every even row in the range example
The example below removes even rows in our sample sheet. That is 2, 4, 6 and so on rows are deleted as we executed this code:
Sub delete_rows() Dim SheetRange As Range 'Setting the range to 4oth row Set SheetRange = Range("A1:D40") TotCount = SheetRange.Rows.Count For i = TotCount To 1 Step -2 Rows(i).Delete Next i End Sub
Find and delete a row by search term
If you want to remove rows based on searching the sheet then you can do this easily.
In the example below, we will search “Out of Stock” words in the cells of range D2 to D40. The program below should remove all rows where this cell value is found. See the sheet below again:
Code:
Sub delete_rows() Dim cell As Range Dim SheetRange As Range 'Setting our range from D2 to D40 cells Set SheetRange = Range("D2:D40") For Each cell In SheetRange If cell.Value = "Out of Stock" Then cell.EntireRow.Delete End If Next cell End Sub
Result:
Similarly, you may add multiple criteria by using another condition in the If statement with And operator.
For example, searching Product name as well as price, etc.
You may learn about If..Else statement here.