How to replace LOOP if I have around hundred thousand lines

Question

I have macro which working good but its super slow, see below. What it is basicly doing is that macro go through every lines in column F and than it will delete the row.

so can I some how replace my loop with something quicker?

Thank you very much and sending best regards from Czech Republic.

Sub Delete2_Find()
    Dim rgFoundCell As Range

    Application.ScreenUpdating = False
    With Sheets("Raw Data")
        Set rgFoundCell = .Range("F:F").Find(what:=Month(Now) - 2)
        Do Until rgFoundCell Is Nothing
            rgFoundCell.EntireRow.Delete
            Set rgFoundCell = .Range("F:F").FindNext
        Loop
    End With
    Application.ScreenUpdating = True


MsgBox "DONE!"
End Sub

use `AutoFilter`: http://stackoverflow.com/questions/22484183/request-for-dynamic-range-solution-when-deleting-based-on-a-filter/22484370#22484370 — Dmitry Pavliv, May 07 '14 at 14:24
If I do it like this `Sub aaa() Dim lastrow As String With ActiveSheet.Range("A1:K" & lastrow) .AutoFilter Field:=6, Criteria1:=Month(Now) - 2 '~~>get all rows except header. Only visible rows would be deleted .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete Shift:=xlUp .AutoFilter Field:=6 End With End Sub` I get error Application not defined or object defined error — Filip Ondo, May 07 '14 at 14:35
where have you set value to `lastrow`? and also `lastrow` should be `Long` rather than `String`? See this: [How to determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) — Dmitry Pavliv, May 07 '14 at 14:37

score 2 · Accepted Answer · edited May 07 '14 at 20:29

2

Could you just union the ranges and delete it at once? Could this help? Something like this:

Option Explicit

Sub Delete2_Find()
    Dim rgFoundCell As Range
    Dim toBeDeted As Range
    Dim firstAddress

    Application.ScreenUpdating = False

    With Sheets("Raw Data").Range("F:F")
        Set rgFoundCell = .Find(what:=Month(Now) - 2)

        If Not rgFoundCell Is Nothing Then
            firstAddress = rgFoundCell.Address

            Do
                If toBeDeted Is Nothing Then
                    Set toBeDeted = rgFoundCell.EntireRow
                Else
                    Set toBeDeted = Union(toBeDeted, rgFoundCell.EntireRow)
                End If

                Set rgFoundCell = .FindNext(rgFoundCell)
                If rgFoundCell Is Nothing Then Exit Do

            Loop While rgFoundCell.Address <> firstAddress

        End If
    End With

    Application.ScreenUpdating = True

    If Not toBeDeted Is Nothing Then _
        toBeDeted.Select ' Delete

    MsgBox "DONE!"
End Sub

edited May 07 '14 at 20:29

Dmitry Pavliv

35,333
13
79
80

answered May 07 '14 at 15:02

Daniel Dušek

13,683
5
36
51

1

`Loop While Not rgFoundCell Is Nothing And rgFoundCell.Address <> firstAddress` not working, because if `rgFoundCell` is `Nothing`, `rgFoundCell.Address` fails (despite the fact that it is MSDN example) – Dmitry Pavliv May 07 '14 at 15:33
@simoco "fails (despite the fact that it is MSDN example)" - happens way more often than it should :( – Blackhawk May 07 '14 at 16:54
@simoco thank you for your comment, but I do not understand what you mean, could you explaine it more in detail please? Do you have an example? – Daniel Dušek May 07 '14 at 17:39
In line `Loop While Not rgFoundCell Is Nothing And rgFoundCell.Address <> firstAddress` even if `rgFoundCell` is `Nothing`, vba evaluates second part of condition: `rgFoundCell.Address <> firstAddress` which would be equal to `Nothing.Address <> firstAddress` and gives you runtime error. – Dmitry Pavliv May 07 '14 at 19:21
@simoco Ok, but rgFoundCell can't be Nothing if at least one cell in the target range contains the searched value. And then FindNext will find this cell again. I simply can't reproduce any problems. Do you have example of data where this problem can be reproduced? In help I read this about Range.FindNext method, remarks : When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address. – Daniel Dušek May 07 '14 at 20:04
ok, here is test workbook: https://www.dropbox.com/s/owxpc56dn4ouhx7/FindTest.xlsm Just run the macro. Problem with msdn example is that they're modifying found data: `c.Value = 5` and in next "iteration" `FindNext` can't find `2` again (because you change it to 2) and `Set c = .FindNext(c)` simply returns `Nothing`. _In my workbook error occurs only if you run macro first time, for running macro second time change `5` back to `2` in range `A1:A100`_ – Dmitry Pavliv May 07 '14 at 20:16
@simoco aha, ok I see. But that is not the case of code in my answer. In this code the data of the cells is not changed and so it finds all occurences without any exception. – Daniel Dušek May 07 '14 at 20:20
maybe, but what if OP desided do modify your code and add single line with changing data? I guess next their Q would be _"I get Object variable or With block not set error"_:) Always make your code reliable) – Dmitry Pavliv May 07 '14 at 20:29
1

@simoco thanks for your edit. Well the code just finds data and unions it to one range. It is good point that the range data can't be modified inside of do-loop, so now the OP knows it :-). – Daniel Dušek May 07 '14 at 20:37
1

@simoco well now I recognized you moved the 'If rgFoundCell Is Nothing' from the 'Loop While' check and added 'Exit Do'. With this change the range data actually can be modified inside of the do-loop so OP can do it if needed, cool! – Daniel Dušek May 08 '14 at 06:56
Hi I am just testing it macro is running will get you know how its ends – Filip Ondo May 08 '14 at 10:52
Well I add one more line in code `toBeDeted.EntireRow.Delete` and now its deleting properly :-) – Filip Ondo May 08 '14 at 11:30
@Filip Ondo you can only remove 'Select' and uncomment the 'Delete' here: toBeDeted.Select ' Delete. Select was just for demostration purposes. – Daniel Dušek May 08 '14 at 12:05

How to replace LOOP if I have around hundred thousand lines

1 Answers1