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
Community
  • 1
  • 1
PerryT
  • 1
  • 1
  • 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`. – David Zemens Jun 21 '18 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")? – David Zemens Jun 21 '18 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. – PerryT Jun 21 '18 at 20:58
  • 1
    There a a _lot_ of other Q/A's about deleting rows fast. One good one [here](https://stackoverflow.com/q/39809939/445425) - don't just look at the accepted answer, the other one is good too. [here](https://stackoverflow.com/q/46547789/445425). [here](https://stackoverflow.com/q/14241882/445425) – chris neilsen Jun 21 '18 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. – David Zemens Jun 21 '18 at 21:18

0 Answers0