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!

 

[mattahmadi62]

Amazing! It worked perfectly!!! Thank you both so so much

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

 

[mattahmadi62]

Does this code apply to all sheets? It works perfect for one sheet, but it doesn't apply to the others. Any simple edit to include all visible sheets?

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

 

[Rick Rothstein]

Does this code apply to all sheets? It works perfect for one sheet, but it doesn't apply to the others. Any simple edit to include all visible sheets?

Your original post did not mention anything about multiple sheets, so I wrote the code to work for the active worksheet... if you do not have many sheets to process, you could just activate the next sheet and run the macro again. If, on the other hand, you have many sheets, then the question is do you want to run the macro against every sheet in the active workbok or just against a select number of them. If a select number, you will need to tells us the names of those sheets or atleast some way to identify them (such as by a common prefix to their names) or I could make the code run against all the selected sheets which, of course, would require you to select the sheets before running the macro. Please advise how you want me to proceed.