Delete row based on condition in VBA Excel – debugvba.com

delete row based on condition vba excel

Objective

To delete a row based on value in a cell.

Approach

In this VBA Excel automation, we aim to delete multiple rows based on the value in  column A in that row. Here we have created some sample data in column A and B. Data in column A has “year” as column name, while column B had “revenue” as column name. This can be seen in the image below.

Code

In the VBA code we have looped through each row, then checked the value in column A for that row, if the value in column A is 2018, then we delete that row. Here we have used the rows property of range object.

Sub DeleteRows()

Dim LastUsedRow As Long

'Find last used row in column A
LastUsedRow = Range("A" & Rows.Count).End(xlUp).Row


'Loop through each row in worksheet
For i = 2 To LastUsedRow
If Range("A" & i).Value = "2018" Then
'Delete the row
Rows(i).Delete
i = i - 1
End If

Range("A1").Select
Next i

End Sub

 

In the image below you on left side you can see that before executing the VBA code we had data for 3 years 2017,2018,2019. On right side you can see that we have deleted the data for 2018.

delete row based on condition vba excel

Note

  1. We can use similar approach for data validation in excel to highlight a row.
  2. We can also delete columns by following similar approach


Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.rows

Post you may like

Debug Error : Object variable or with block variable not set in VBA Excel