Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel 2010 Fast Delete

Posted on 2013-05-24
20
Medium Priority
?
213 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 8

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 8

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 2000 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

972 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