0

I am wanting to speed up a looping code that is removing Rows that contain a #DIV/0! error in cells BU6:BU1783. This code takes an extremely long time to run as it seems to keep updating other sheets on each deleted Row.

I need help to work out code that will calculate the 1st error (or blank) row, then make that the starting Row to delete rows beyond that up to a fixed row without it having to loop.

The data in the data table will always start from Row 6 but the final Row is variable but no greater that Row 1783.

The formula in Column "BU" was a simple filtering formula to return a 1 or 0 for in sheet filtering. I can use other Columns like "B" which would be Blank to also do this.

An example would be data in rows 6 to 100 and I want to delete rows 101 (This is the variable) to 1783 (Constant)

At the moment I am using the following code:

Application.ScreenUpdating = False
Application.Calculation = xlManual

Dim d As Long
For d = 1783 To 2 Step -1
    If IsError(Sheets("Raw Data").Range("I" & d)) Then
        Rows(d).EntireRow.Delete
    End If
Next d

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

Thanks for your help in advance.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Jason Seah
  • 1
  • 1
    Consider using `Union` and only deleting at the end... see [this question](https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba) for the approach. – BigBen May 21 '20 at 02:28
  • When I'm in that kind of situation, I copy value the column with the formula to another column, filter this column with the criteria "#DIV/0!", then delete the filter result. – karma May 21 '20 at 03:11

0 Answers0