Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

What's the VB code or macro for performing this --

the previous question from months back

"The 3 conditions are 'below a certain percentage/number.' It has to meet all conditions."

didnt seem specific enough so details were requested, so for example

if A2 is <20%
if B2 is >30%
if C2 is >10
if all of the above 3 conditions are meet
delete row 2

this whole process is repeated on every subsequent row

share|improve this question
up vote 0 down vote accepted

A loop will work, but an autofilter will work much faster. Assuming that you have headers in row(1) and no empty rows, this will delete the rows meeting your criterias:

Sub autofilterAndDelete()
dim lLastRow as long


With Range("A1:C1").resize(lLastRow)
    .AutoFilter field:=1, Criteria1:="<0.2"
    .AutoFilter field:=2, Criteria1:=">.3"        
    .AutoFilter field:=3, Criteria1:=">10"

    if application.worksheetfunction.subtotal(3,.columns(1))>1 then _
        .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

End With
End Sub
share|improve this answer
guess i'll have to go with and try the faster one – kittensatplay Oct 4 '12 at 19:48
the code gave an error on this line -- .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete – kittensatplay Oct 4 '12 at 20:44
Error will happen if there are no cells matching the criteria, or if you have empty rows in the middle of your data. I'm adjusting the code to fix that. – nutsch Oct 4 '12 at 21:43
what happened was everything on that sheet got delete, i'll try again – kittensatplay Oct 4 '12 at 21:48

Here's a sample code to do what you said, use .2 as 20% etc because that's the actual value. Substitute Sheets(1) with the actual sheet you're doing this on.

Sub DeleteRows()
    Dim x As Long
    With Sheets(1)
        For x = .UsedRange.Rows.Count To 2 Step -1
            If .Cells(x, 1) < 0.2 And .Cells(x, 2) > 0.3 And .Cells(x, 3) > 10 Then
            End If
    End With
End Sub
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.