To delete a row based on value in a cell.
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.
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.
- We can use similar approach for data validation in excel to highlight a row.
- We can also delete columns by following similar approach
Post you may like