Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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
application.screenupdating=false

lLastRow=cells(rows.count,1).end(xlup).row

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

    .AutoFilter
End With
application.screenupdating=true    
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
                .Rows(x).Delete
            End If
        Next
    End With
End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .