Excel VBA optimisation for hidden rows

Question

I have a macro that iterates through some rows, to update the colouring of data points in a related chart. The rows can be hidden by the user, so it checks the hidden value, i.e.

Do While wsGraph.Cells(RowCounter, 1) <> ""
    If wsGraph.Rows(RowCounter).Hidden = False Then
        'code here
    End If
    RowCounter = RowCounter + 1
Loop

This code takes 69 seconds to run. If I take the test for the hidden row out, it takes 1 second to run.

Is there a better way to do this test, otherwise I will have to tell the users they can't use the hide function (or deal with a 69 second delay).

Thanks

Here's the full code, as requested.

The graph is a bar graph, and I colour the points based on the values being in certain ranges, eg: over 75% = green, over 50% = yellow, over 25% = orange, else red. There's a button on the form to recolour the graph, that executes this code.

If someone filters the data table, what's happening is this: say the first 20 rows were over 75%, and were initially coloured green. After filtering the table, say only the first 5 are over 75%. The graph still shows the first 20 as green. So this button with the macro recolours the bars.

' --- set the colour of the items
Dim iPoint As Long
Dim RowCounter As Integer, iPointCounter As Integer
Dim wsGraph As Excel.Worksheet
Set wsGraph = ThisWorkbook.Worksheets(cGraph5)
wsGraph.ChartObjects("Chart 1").Activate
' for each point in the series...
For iPoint = 1 To UBound(wsGraph.ChartObjects("Chart 1").Chart.SeriesCollection(1).Values)
    RowCounter = 26
    iPointCounter = 0
    ' loop through the rows in the table
    Do While wsGraph.Cells(RowCounter, 1) <> ""
        ' if it's a visible row, add it to the counter, if it's the same counter as in the series, exit do
        If wsGraph.Rows(RowCounter).Hidden = False Then
            iPointCounter = iPointCounter + 1
            If iPointCounter = iPoint Then Exit Do
        End If
        RowCounter = RowCounter + 1
    Loop
    ' colour the point from the matched row in the data table
    Dim ColorIndex As Integer
    If wsGraph.Cells(RowCounter, 5) >= 0.75 Then
        ColorIndex = ScoreGreen
    ElseIf wsGraph.Cells(RowCounter, 5) >= 0.5 Then
        ColorIndex = ScoreYellow
    ElseIf wsGraph.Cells(RowCounter, 5) >= 0.25 Then
        ColorIndex = ScoreOrange
    ElseIf wsGraph.Cells(RowCounter, 5) >= 0 Then
        ColorIndex = ScoreRed
    Else
        ColorIndex = 1
    End If
    ActiveChart.SeriesCollection(1).Points(iPoint).Interior.ColorIndex = ColorIndex
Next

something else must be happening for the time to jump from 1 sec to 69 secs; your loop will only execute code here if Rows(RowCounter) is hidden - can you supply some more detail for code here ? — whytheq
– whytheq, Commented Jun 16, 2012 at 15:58
@whytheq: If I have no rows filtered/hidden in the data table, and then I run it with the "hidden" check commented out, it's the same effective result. The difference in timing is under 1 second vs 23 seconds, for 279 data table rows. — Sean
– Sean, Commented Jun 16, 2012 at 19:22

chris neilsen · Accepted Answer · 2012-06-16 11:30:25Z

2

Try Special Cells

Sub LoopOverVisibleCells()
    Dim r As Range
    Dim a As Range
    dim cl As Range

    Set r = ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeVisible)

    For Each a In r.Areas
        For Each cl In a
            ' code here
        Next
    Next

End Sub

answered Jun 16, 2012 at 11:30

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sean · Accepted Answer · 2012-06-16 20:10:13Z

0

This is what I've done, using Chris's suggestion. It doesn't answer why the hidden check is so slow, but it's a more efficient way of doing the recolouring:

Dim myrange As range
Set myrange = wsGraph.range("E26:E304").SpecialCells(xlCellTypeVisible)
Dim i As Integer
For i = 1 To myrange.Rows.Count
    If myrange.Cells(i, 1) >= 0.75 Then
        ColorIndex = ScoreGreen
    ElseIf myrange.Cells(i, 1) >= 0.5 Then
        ColorIndex = ScoreYellow
    ElseIf myrange.Cells(i, 1) >= 0.25 Then
        ColorIndex = ScoreOrange
    ElseIf myrange.Cells(i, 1) >= 0 Then
        ColorIndex = ScoreRed
    Else
        ColorIndex = 1
    End If
    ActiveChart.SeriesCollection(1).Points(i).Interior.ColorIndex = ColorIndex
Next i

edited Jun 16, 2012 at 20:10

answered Jun 16, 2012 at 19:42

Sean

15.2k17 gold badges83 silver badges139 bronze badges

Collectives™ on Stack Overflow

Excel VBA optimisation for hidden rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related