It’s impressive how often this question has been made on the forum (www.tomavasquez.com.br/forum).
I’ve saw many solutions posted, each one different from another. Those were not bad solutions, but they didn’t solve all conditions.
Well, I decided to take the best of it togheter in one piece of generic code and the result is the macro below:
Function DeleteRowsByCriteria(ByVal firstRow As Integer, ByVal lastRow As Integer, ByVal criteriaColumn As Integer, ByVal criteria As String) As Integer Dim deletedRows As Integer Dim i As Integer deletedRows = 0 With ActiveSheet i = firstRow While i < lastRow If CStr(.Cells(i, criteriaColumn).Value) = criteria Then .Rows(i).Delete deletedRows = deletedRows + 1 Else i = i + 1 End If Wend End With DeleteRowsByCriteria = deletedRows End Function
- firstRow – the index of the first row of the Range to be evaluate
- lastRow – the index of the last row of the Range to be evaluate
- criteriaColumn – the index of the column in the Range to be evaluate
- criteria – the criteria value
With these parameters, the loops over all rows in the range (based on firstRow and lastRow parameters), for each row, it evaluate the cell column (based on criteriaColumn parameter) and compare this value with the value in criteria parameter. If the comparison matches, the line will be deleted. As a final result, the function returns the quantity of rows deleted on the list.
Below you can check an example of how use the macro above:
Sub Execute() MsgBox DeleteRowsByCriteria (1, 200, 6, "London") & " rows has been deleted" End Sub
On the code above, all rows in the current worksheet between line 1 and 200, wich have the value “London” in the colunm 6 or “F” will be deleted.
Be careful with this code. It does not ask for confirmation or data validation either. Configure it very carefully before run it.
Enjoy the code!