StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExcel VBA optimisation for hidden rows
text
Body
copied!<p>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.</p> <pre><code>Do While wsGraph.Cells(RowCounter, 1) <> "" If wsGraph.Rows(RowCounter).Hidden = False Then 'code here End If RowCounter = RowCounter + 1 Loop </code></pre> <p>This code takes 69 seconds to run. If I take the test for the hidden row out, it takes 1 second to run.</p> <p>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).</p> <p>Thanks</p> <hr> <p>Here's the full code, as requested. </p> <p>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. </p> <p>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. </p> <pre><code>' --- 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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload