0

I am using a macro to pull data from 4 tabs into one tab. When the data pulls over, there are several hundred rows of zero's that I am trying to delete. The main issue I am running into and can't figure out is that the number of rows varies virtually every time I pull it, so if I use a row (like 1570:1570 in the second scenario) below I miss some data or delete some date. If I identify and run (as in the first scenario) the loop takes FOREVER to run. I have used both codes below, with reasons why I don't like them. Any help is appreciated (I am a TOTAL newbie to VBA btw).

-This is scenario 1 which takes so long to loop (usually around 8K-10K rows of data) And scenario 2 as follows: -This is scenario 2 which works quickly, but either deletes valid data or doesn't delete all rows with 0's. As you will be able to tell, this one I did recording the macro as I did it in excel.

Application.CutCopyMode = False 

Dim wbkX As Workbook
Dim wksX As Worksheet
Set wbkX = ActiveWorkbook
Set wksX = wbkX.Worksheets("Merge")
    lRow = wksX.UsedRange.Rows.Count 
    lCol = wksX.UsedRange.Columns.Count 

wksX.Range(Cells(1, 1), Cells(1, lCol)).AutoFilter Field:=1 

For x = 1 To lRow 'Loop's through every row from 1 to Last Row.  
    If wksX.Cells(x, 1).Value = 0 Then 
        wksX.Rows(x).Delete 
        x = x - 1 
    End If
Next x


Sub DeleteZeros()

' DeleteZeros Macro
ActiveSheet.Range("$A$1:$G$27997").AutoFilter Field:=1, Criteria1:="0"
Rows("1571:1571").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveSheet.Range("$A$1:$G$6277").AutoFilter Field:=1
End Sub
Improve this question
5
  • In the first method, I would figure that For x = 1 To lRow is wrong if you're deleting from a collection, it's imperative to iterate backwards, For x = lRow to 1 Step -1. Otherwise, you're going to skip iterations as the collection is re-indexed on each Delete. Commented Jun 21, 2018 at 20:48
  • Can you revise your explanation of what's wrong with the second method? Do you think it has to do with the fact that you've hard-coded the ranges ("A1:G27997" and "1571")? Commented Jun 21, 2018 at 20:50
  • Yes David, and thank you. It's because they are hard coded from the macro recording. So basically when I was recording the Macro, I added a filter to line 1, sorted by "0" in column A and deleted all those rows. It just happened to be row 1571 at the time, but will vary pretty much daily. Regarding A1:G27997 pretty much the same, I selected the columns and rows that were relevant, columns will always be A:G, but the rows will always vary here as well. Commented Jun 21, 2018 at 20:58
  • 1
    There a a lot of other Q/A's about deleting rows fast. One good one here - don't just look at the accepted answer, the other one is good too. here. here Commented Jun 21, 2018 at 21:10
  • after you apply the filter the first row with 0 value can be derived from the Find method. if the rest of that macro works, provided you have that row correctly identified, that's the easiest solution versus modifying another approach. Commented Jun 21, 2018 at 21:18
Related questions
10
How to delete rows in Excel based on criteria using VBA?
1
Excel VBA - Delete Rows Based on Criteria
3
deleting specific tabs in excel
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.