0

Code is looping through fairly large data (1000 rows by 20-ish columns) and deleting rows where there is not an error in the cell but currently takes 20 minutes to execute. Looking for a way to speed up the process by removing the need for offset and select in the code.

I have tried turning auto-calculation off/on before and after the code but this did not noticeably affect run-time. Screen updating is already off.

Range("A6").Select
Do Until IsEmpty(ActiveCell)
    If IsError(ActiveCell) Then
        ActiveCell.Offset(1, 0).Select
    Else
        ActiveCell.EntireRow.Delete
    End If
Loop

Leaves rows where the cell in Range A is N/A (IsError). Runs as expected but takes a full 20 minutes to run.

Any help is appreciated.

Toby
  • 3
  • 3
  • 2
    Why not just autofilter and delete the visible rows in one hit, either manually or in code? – Rory Feb 07 '19 at 10:56
  • 2
    It's better to use cell(row,col) instead of Activecell. You can control it with index to iterate the rang u want. – Adirmola Feb 07 '19 at 10:58
  • 1
    Alternatively to what Rory commented, you could also populate an array with all rows with an error and delete all rows at once, after the loop has exited – Tim Stack Feb 07 '19 at 11:00
  • See [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Nacorid Feb 07 '19 at 11:10
  • 1
    Quickest way would probably be to sort on Column A such that the blanks are pushed to the bottom in one large block. Then find the first blank row and delete everything below in one action. – CLR Feb 07 '19 at 11:13
  • quick win: disable calculation before the loop ! `Application.Calculation = xlManual` – iDevlop Feb 07 '19 at 11:28
  • Thanks all - I will look into each suggestion! I have already tried disabling the calculations, that came up in my research but it didn't have any noticeable effect. – Toby Feb 07 '19 at 12:00

2 Answers2

3

SpecialCells can quickly find all formulas returning booleans, numbers or text while excluding errors.

dim rng as range

with worksheets("sheet1")
    with .range(.cells(6, "A"), .cells(.rows.count, "A").end(xlup))
        on error resume next
        set rng = .specialcells(xlCellTypeFormulas, xlLogical+xlNumbers+xlTextValues)
        on error goto -1
        if not rng is nothing then
            rng.entirerow.delete
        end if
    end with
end with
user11026105
  • 166
  • 3
  • Thank you for the suggestion - I haven't encountered specialcells yet, i'll look into this! – Toby Feb 07 '19 at 12:01
  • Thank you for the specialcells - I'm currently testing the following and seems to be working so far, no looping required: Worksheets("Working Report").Range("A6:A" & lstrow).SpecialCells(xlCellTypeConstants, Value:=xlTextValues).EntireRow.Delete – Toby Feb 11 '19 at 12:05
0
i=6
With ActiveSheet
     Do Until IsEmpty(.cells(i,1))
        If IsError(.cells(i,1)) Then
             i=i+1
         Else
            .Row(i).Delete
         End If
    Loop
End with
Adirmola
  • 783
  • 5
  • 15
  • 2
    This will probably not work correctly. To avoid issues and compications when deleting rows, always start from the last row and go up. – iDevlop Feb 07 '19 at 11:23
  • @Patrick Honorez not sure why it will not work correctly. I didn't increase the index when deleting the row – Adirmola Feb 07 '19 at 11:59
  • Although it might work in this instance but it's considered bad practice. As per @PatrickHonorez suggestion, it's always safer to delete items from the bottom up. Also, your end condition is `Do Until F1 is not empty`.. again, it might work but what if `F1` is empty on a row but it's not the last row?. Better way to do it would be to get row count as per `user11026105` suggestion – Zac Feb 07 '19 at 13:42
  • Deleting from bottom-up is only problematic if you're using something like a `For-Next` loop as the index increases whether the row is deleted or not. In this case, `i` doesn't change if the row is deleted and therefore the 'usual' issue of skipping won't arise. – CLR Feb 07 '19 at 14:24