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.
Share Share Tweet