VBA : How to Delete a Row

This tutorial explains how to delete a row in Excel using VBA.

You can download the following dataset to practice.

Rows(RowNumber).Delete is used to delete a row. Let's consider a few examples to understand its practical application.

1. Delete a Specific Row

The following code can be used to delete the third row in 'Sheet1' of the dataset :

Sub DeleteSpecificRow()
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
     ws.Rows(3).Delete
End Sub

Press Run or F5 to run the above macro.

2. Removing Rows based on a Condition

The following code can be used to delete rows in column "A" of 'Sheet2' that have values greater than '50' :

Sub deleterows50()
    Dim lastRow As Long
    Dim i As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2")
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = lastRow To 2 Step -1
    
        If ws.Cells(i, 1).Value > 50 Then
            Rows(i).Delete
        End If
    Next i
End Sub

3. Removing Empty Rows

The following code can be used to delete empty rows in 'Sheet1' of the dataset :

Sub DeleteEmptyRows()
    Dim lastRow As Long
    Dim i As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = lastRow To 1 Step -1
            If IsEmpty(ws.Cells(i, 1).Value) Then
            Rows(i).Delete
        End If
    Next i
End Sub

IsEmpty() function is used to check missing values.

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn