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! :)
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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
36,685
Office Version
  1. 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
36,685
Office Version
  1. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,981
Members
414,115
Latest member
SFUser

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