VBA to delete rows with a formula that equals blank

[NRIANS2123]

I've created a button that incorporates a bunch of functions. Everything is working, but the process started taking an extremely long time after I added the function below. What I'm do with this function is deleting all of the rows that have formulas in them that end up equaling a blank cell. Does anyone have any ideas on a better approach?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim i As Long, finalRow As Long
finalRow = Cells(Rows.Count, 1).End(xlUp).Row[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] With Worksheets("Backlog Report (Regular)")
For i = finalRow To 2 Step -1
If Range("C" & i).Value = "" Then
Range("C" & i).EntireRow.Delete
End If
Next i
End With[/FONT]

 

[Joe4]

You can help speed things up by temporarily disabling calculations and screen updates, i.e.

[COLOR=#333333]Dim i As Long, finalRow As Long[/COLOR][COLOR=#333333]

[/COLOR][COLOR=#ff0000]Application.Calculation = xlCalculationManual[/COLOR]
[COLOR=#ff0000]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]
finalRow = Cells(Rows.Count, 1).End(xlUp).Row[/COLOR]
[COLOR=#333333]With Worksheets("Backlog Report (Regular)")
For i = finalRow To 2 Step -1
If Range("C" & i).Value = "" Then
Range("C" & i).EntireRow.Delete
End If
Next i
[/COLOR][COLOR=#333333]End With[/COLOR]

[COLOR=#ff0000]Application.ScreenUpdating = True[/COLOR]
[COLOR=#ff0000]Application.Calculation = xlCalculationAutomatic[/COLOR]

 

[Rick Rothstein]

What I'm do with this function is deleting all of the rows that have formulas in them that end up equaling a blank cell.

There might be a quick non-looping way to do this, but it depends on what your formula is returning when it is not returning a blank (""). So, what kind of values does your formula return?

 

[NRIANS2123]

It's a simple formula carrying data over from another sheet with an IF function to return a blank cell when there's no data to carry over. What I'm doing is taking a report from our ERP system and dumping the report into one sheet. The second sheet that has the formula below is simply taking the dumped data and pulling out the information I want to see.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(Report!B27=0,"",Report!B27)[/FONT]

 

[NRIANS2123]

Thanks. That did help speed it up some.

 

[Rick Rothstein]

It's a simple formula carrying data over from another sheet with an IF function to return a blank cell when there's no data to carry over. What I'm doing is taking a report from our ERP system and dumping the report into one sheet. The second sheet that has the formula below is simply taking the dumped data and pulling out the information I want to see.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(Report!B27=0,"",Report!B27)[/FONT]

Okay, from that formula I conclude the values that are not blanks are numbers. If that is correct, then see if this macro is any faster than what you have now...

[table="width: 500"]
[tr]
	[td]Sub DeleteFormulaRowsDisplayBlanks()
  On Error GoTo NoBlanks
  Range("C2", Cells.Find("*", , xlFormulas, , xlRows, xlPrevious)).SpecialCells(xlFormulas, xlTextValues).EntireRow.Delete
NoBlanks:
End Sub[/td]
[/tr]
[/table]