1

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

2 Answers 2

0

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
4
  • guess i'll have to go with and try the faster one Oct 4, 2012 at 19:48
  • the code gave an error on this line -- .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete Oct 4, 2012 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, 2012 at 21:43
  • what happened was everything on that sheet got delete, i'll try again Oct 4, 2012 at 21:48
2

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

You must log in to answer this question.

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