Solved

Excel 2010 Fast Delete

Posted on 2013-05-24
20
198 Views
Last Modified: 2013-05-28
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.
0
Comment
Question by:Jagwarman
  • 9
  • 9
  • 2
20 Comments
 
LVL 10

Expert Comment

by:plummet
Comment Utility
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
 

Author Comment

by:Jagwarman
Comment Utility
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
 

Author Comment

by:Jagwarman
Comment Utility
plummet, I mis read what you said, however, your solution takes out the header row.
0
 
LVL 10

Expert Comment

by:plummet
Comment Utility
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
 
LVL 7

Expert Comment

by:MSmax
Comment Utility
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
 

Author Comment

by:Jagwarman
Comment Utility
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
 
LVL 10

Expert Comment

by:plummet
Comment Utility
Did you try my solution?
0
 
LVL 7

Expert Comment

by:MSmax
Comment Utility
Can you please post your excel file so I can see why its not working?

Regards,

MSMax
0
 

Author Comment

by:Jagwarman
Comment Utility
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
 

Author Comment

by:Jagwarman
Comment Utility
MS MAX

I can't post my file will build a dummy file and post it later
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Expert Comment

by:plummet
Comment Utility
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
 

Author Comment

by:Jagwarman
Comment Utility
plummet, yes I copy pasted your code
0
 
LVL 10

Expert Comment

by:plummet
Comment Utility
Sorry to hear that - I'll check it again and get back to you
0
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Jagwarman
Comment Utility
That is very odd, I guess it must have been me.

Thanks
0
 
LVL 10

Expert Comment

by:plummet
Comment Utility
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
 

Author Comment

by:Jagwarman
Comment Utility
plummet, would this work for blank rows and if so how would I change it?

thanks
0
 
LVL 10

Expert Comment

by:plummet
Comment Utility
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
 

Author Comment

by:Jagwarman
Comment Utility
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
 
LVL 10

Expert Comment

by:plummet
Comment Utility
I'll work out some VBA and will try to keep an eye out for the new request
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now