Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

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.