# Excel VBA speed up code to hide rows/delete color

I am using following code to delete color and hide rows that include several criteria before exporting sheets to pdf's. Is there any way to speed up this process as it is taking quite a lot of time to process. Especially in situations when I have several sheets in one workbook and to apply this on each sheet = "printed page".

Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
For Each cell In myRange
myRange.Interior.ColorIndex = 0
If cell.HasFormula = True And cell.Value = "" And cell.EntireRow.Hidden = False Then Rows(cell.Row).EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub

• Welcome to Code Review! What does your data look like? Can you share an (anonimized) sample? How many rows per worksheet are we talking about? – Mast Nov 5 '18 at 10:34
• My data is simply text. I have "Filling form" and "Printing form". User is filling "Filling form" and on "Printing form" I have formulas with displaying text or "" if some fields are left empty on "Filling form". I am hiding rows that have "" cells to get more better looking end result. We are talking about 450 rows on sheet. – hatman Nov 5 '18 at 13:02
• The instinctive thing would be to loop over all the cells and save the ones that need hiding in an array. Then UNION them all and only make one call to .Hidden, which will I'm sure be a bottleneck in your code. Similarly, set the colour index of the whole range at once. – Greedo Nov 7 '18 at 10:18

Your test for hidden rows suggests you may already have hidden rows.

Nested IF statements could reduce the number of tests to be done.

As you are removing color from all cells, do it before the loop:

Sub Color()
Dim myRange As Range
Dim cell As Range
Application.ScreenUpdating = False
Set myRange = ThisWorkbook.Sheets("Print version").Range("Print_Area")
myRange.Interior.ColorIndex = 0
For Each cell In myRange
if Not cell.EntireRow.Hidden then
'  once hidden, no more cell values will be tested on the row
If cell.HasFormula = True And cell.Value = "" then
Rows(cell.Row).EntireRow.Hidden = True
endif
endif
Next
Application.ScreenUpdating = True
End Sub