2
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ 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? \$\endgroup\$ – Mast Nov 5 '18 at 10:34
  • \$\begingroup\$ 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. \$\endgroup\$ – user7202022 Nov 5 '18 at 13:02
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – Greedo Nov 7 '18 at 10:18
0
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.