Excel 2010 Fast Delete

I am looking for some VBA code that will quickly delete all rows where there is a 0 in column B.

I am using the following code but as there are over 150k rows in the file each day it takes a while to complete.

Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Not Range("B" & i).Value <> 0 Then Rows(i).Delete
Next i

Thanks In advance.
JagwarmanAsked:
Who is Participating?
 
plummetConnect With a Mentor Commented:
I've had another quick try, and this seems to work every time:

    Dim rng As Range

    Columns("B:B").Select
    Selection.AutoFilter Field:=1, Criteria1:=0
    With Selection
        Set rng = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 1, .Columns.Count - 1))
    End With
    rng.Select
    rng.Delete shift:=xlUp

Open in new window

0
 
plummetCommented:
How about something along these lines:


    Columns("B:B").Select
    Selection.AutoFilter Field:=1, Criteria1:=  0
    Cells.Select
    Selection.Delete Shift:=xlUp

I think that should be much quicker.

Regards
John
0
 
JagwarmanAuthor Commented:
I need it as part of a Macro because there are a dozen or so other things going on and I don't want the user to stop half way through the macro to do that manually and then go back into the macro to complete the process.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JagwarmanAuthor Commented:
plummet, I mis read what you said, however, your solution takes out the header row.
0
 
plummetCommented:
OK, didn't know you had one. Try this:

  Dim rng As Range

  Columns("B:B").Select
  Selection.AutoFilter Field:=1, Criteria1:=  0
  With UsedRange
        Set rng = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 1, .Columns.Count - 1))
   End With
   rng.Select
   Selection.Delete shift:=xlUp
0
 
MSmaxImplementation ConsultantCommented:
Hey try below and let me know if it works for you

Sub Delete_Rows()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim LR As Long, i As Long
    
    
    Set wb = ThisWorkbook
    'Change the "Sheet1" to anything you like
    Set ws = wb.Worksheets("Sheet1")
    
    'Get the last used row
    LR = ws.Range("B" & Rows.Count).End(xlUp).Row
    
    'Turn off the filter
    ws.AutoFilterMode = False
    
    'Change it to select all the columns you need
    Set rng = ws.Range("A1:B" & LR)
    
    rng.AutoFilter Field:=2, Criteria1:="0"
    
    'if no row meting criteria go to Exit
    On Error GoTo Exit1
    Set rng = ws.Range("A2:B" & LR).SpecialCells(xlCellTypeVisible)

    rng.EntireRow.Delete
    
Exit1:
    'Reset errors
    On Error GoTo 0
    'Turn off the filter
    ws.AutoFilterMode = False

End Sub

Open in new window

0
 
JagwarmanAuthor Commented:
MSMax I get a VB error

Run-time error

The command could not be completed by using the range specified. Select a single cell within the range and try the command again.
0
 
plummetCommented:
Did you try my solution?
0
 
MSmaxImplementation ConsultantCommented:
Can you please post your excel file so I can see why its not working?

Regards,

MSMax
0
 
JagwarmanAuthor Commented:
Plummet

it did and got this error "Object Required" on this row
Set rng = Range(.Cells(2, 1), .Cells(1, 1).Offset(.Rows.Count - 1, .Columns.Count - 1))
0
 
JagwarmanAuthor Commented:
MS MAX

I can't post my file will build a dummy file and post it later
0
 
plummetCommented:
Oh dear, it should work fine.

Did you definitely have the line:

With Usedrange

above the line that errors? Make sure it's exactly as my example.
0
 
JagwarmanAuthor Commented:
plummet, yes I copy pasted your code
0
 
plummetCommented:
Sorry to hear that - I'll check it again and get back to you
0
 
JagwarmanAuthor Commented:
That is very odd, I guess it must have been me.

Thanks
0
 
plummetCommented:
No, you were right when I tried my original code it failed just as you said. I am on a different PC today, so who knows.

Anyway I'm glad it helped!
0
 
JagwarmanAuthor Commented:
plummet, would this work for blank rows and if so how would I change it?

thanks
0
 
plummetCommented:
I don't think so, it's basically doing a filter in code and I don't think you can filter for blank rows. However it can be done in code a different way.

Your username implies you're either a Jaguar fan or work for them, just wondering because I work for Jaguar Land Rover :-)
0
 
JagwarmanAuthor Commented:
I drive an X type but have had XJ's and Sovereigns in the past. I have a funny noise coming from rear near side at the moment so need to get it in to get sorted soon.

I will post a new request later maybe

thanks
0
 
plummetCommented:
I'll work out some VBA and will try to keep an eye out for the new request
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.