VBA to remove/delete based off single criteria without loop

[mattahmadi62]

Hi all,

I'm okay with Excel, but I'm just getting into utilizing VBA and I've been searching high and low for a simple VBA code that will remove/delete rows based off a single criteria without loop as there are over 40,000 rows. I tried a couple that I found onilne, and adapting them to my criteria range, but no luck. (All the ones that I found that work use loop and it takes about 15 mins to run through the entire spreadsheet)

I would like to maintain my first row as it's my headers. My single criteria is to remove all rows that have "NO" in column D.

I'm sure it's quite simple - thank you for all those that took the time to assist!

 

[Scott Huish]

I went with columns A through T, change as necessary:

Sub Macro1()
With Columns("A:T")
    .AutoFilter
    .AutoFilter Field:=4, Criteria1:="NO"
    Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With
End Sub

 

[mattahmadi62]

I went with columns A through T, change as necessary:

Sub Macro1()
With Columns("A:T")
    .AutoFilter
    .AutoFilter Field:=4, Criteria1:="NO"
    Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With
End Sub

Thank you! But i'm getting an error message. It filters perfectly, but then (I believe) as it wants to move to delete the findings, it provides an error showing an "X" and just "400".

 

[mattahmadi62]

Bump

 

[Scott Huish]

I tested it with XL2010. Can you post the code as you have it now?

 

[mattahmadi62]

This is what i'm using in the code for the spreadsheet

Sub Macro1()
With Columns("A:T")
    .AutoFilter
    .AutoFilter Field:=4, Criteria1:="NO"
    Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With
End Sub

Exactly how you entered it, and the error message doesn't have any words - just "400"

 

[mattahmadi62]

Also using Excel 2007

This is what i'm using in the code for the spreadsheet

Sub Macro1()
With Columns("A:T")
    .AutoFilter
    .AutoFilter Field:=4, Criteria1:="NO"
    Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With
End Sub

Exactly how you entered it, and the error message doesn't have any words - just "400"

 

[Scott Huish]

The only way I could get it to error at all, and it's a different error is after I put the code in a standard module and run it on a brand new completely blank sheet.

 

[Rick Rothstein]

The only way I could get it to error at all, and it's a different error is after I put the code in a standard module and run it on a brand new completely blank sheet.

The OP has over 40,000 rows of data... if he is using XL2007 or earlier, he might be running into the 8192 areas limit for the SpecialCells function.

 

[Rick Rothstein]

See if this code works...

Sub RemoveRowWhereBlankInColumnA()
  Dim X As Long, LastRow As Long
  Const ChunkSize As Long = 16000
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  Columns("D").Replace "NO", "", xlWhole
  On Error Resume Next
  For X = 1 + ChunkSize * Int(LastRow / ChunkSize) To 1 Step -ChunkSize
    Cells(X, "D").Resize(ChunkSize).SpecialCells(xlBlanks).EntireRow.Delete
  Next
  On Error GoTo 0
End Sub