Excel VBA speed up code to hide rows/delete color

[mrwad]

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

 

[MARK858]

Maybe the below is some improvement (untested)...

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

Test on a copy of your worksheet

 

[Rick Rothstein]

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

There may be a way to dramatically sped this code up, but what I am thinking about depends on what results your formulas are outputting when they are not outputting the empty text string ("")... are they outputting numbers (this includes dates) only that is, no text values)?

 

[mrwad]

There may be a way to dramatically sped this code up, but what I am thinking about depends on what results your formulas are outputting when they are not outputting the empty text string ("")... are they outputting numbers (this includes dates) only that is, no text values)?

There are different types of values text and numbers. So some of the cells are outputting text and some numbers.

 

[Rick Rothstein]

There are different types of values text and numbers. So some of the cells are outputting text and some numbers.

Then what I had in mind won't work. I can give you one suggestion though. You can take this line of code out of the For..Next loop and execute it just before or after the loop...

myRange.Interior.ColorIndex = 0