Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel 2010 Fast Delete

Posted on 2013-05-24
20
205 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
ID: 39193588
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
ID: 39193605
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
ID: 39193618
plummet, I mis read what you said, however, your solution takes out the header row.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
LVL 10

Expert Comment

by:plummet
ID: 39193661
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
ID: 39193752
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
ID: 39194564
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
ID: 39194748
Did you try my solution?
0
 
LVL 7

Expert Comment

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

Regards,

MSMax
0
 

Author Comment

by:Jagwarman
ID: 39194842
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
ID: 39194846
MS MAX

I can't post my file will build a dummy file and post it later
0
 
LVL 10

Expert Comment

by:plummet
ID: 39195045
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
ID: 39198845
plummet, yes I copy pasted your code
0
 
LVL 10

Expert Comment

by:plummet
ID: 39198975
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
ID: 39198981
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
ID: 39199026
That is very odd, I guess it must have been me.

Thanks
0
 
LVL 10

Expert Comment

by:plummet
ID: 39199069
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
ID: 39201712
plummet, would this work for blank rows and if so how would I change it?

thanks
0
 
LVL 10

Expert Comment

by:plummet
ID: 39201899
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
ID: 39201964
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
ID: 39202286
I'll work out some VBA and will try to keep an eye out for the new request
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
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 shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

839 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