VBA to remove/delete based off single criteria without loop

mattahmadi62

New Member
Joined
Jun 15, 2012
Messages
18
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! :)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I went with columns A through T, change as necessary:

Code:
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

New Member
Joined
Jun 15, 2012
Messages
18
I went with columns A through T, change as necessary:

Code:
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".
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

mattahmadi62

New Member
Joined
Jun 15, 2012
Messages
18
This is what i'm using in the code for the spreadsheet
Code:
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

New Member
Joined
Jun 15, 2012
Messages
18

ADVERTISEMENT

Also using Excel 2007
This is what i'm using in the code for the spreadsheet
Code:
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,866
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,866
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
See if this code works...
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,512
Messages
5,832,178
Members
430,114
Latest member
kefier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top