How to Remove Single/Multiple Rows by Delete method in VBA

How to delete rows in VBA?

• Excel rows can be deleted by using 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 deleting a single row by using Delete method of Worksheets.Rows for the active worksheet.

Our sample sheet is as shown below:

For that, you may simply 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:

Rows("3:6").Delete

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 to 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:

Rows(4).EntireRow.Delete

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:

How it worked?

• 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.