VBA - Avoiding for loop

Question

I have the following data in an excel worksheet, in columns A, B and C respectively.

+-----------+--------------+----------------+
| RangeName |    Clear?    | Value if Clear |
+-----------+--------------+----------------+
| Name1     | DO NOT CLEAR |                |
| Name2     |              |              6 |
| Name3     |              |              7 |
| Name4     | DO NOT CLEAR |                |
| Name5     | DO NOT CLEAR |                |
| Name6     | DO NOT CLEAR |                |
| Name7     | DO NOT CLEAR |                |
| Name8     | DO NOT CLEAR |                |
| Name9     |              |              5 |
| Name10    |              |              9 |
+-----------+--------------+----------------+

Theres a "clear" macro which checks for each excel range name, if column B says "DO NOT CLEAR" then it will skip and do nothing, if it is blank then it will clear the range name and set the range name value to column C. The code is as follows:

For i = 1 To MaxRowCount

    Select Case Range("RngeTbl").Cells(i, 2).Value
    Case "DO NOT CLEAR" 'do nothing
    Case Else 'set to default value
        Range(Range("RngeTbl").Cells(i, 1).Value).Value = Range("RngeTbl").Cells(i, 3).Value
    End Select

Next i

However, the number of range names is increasing massively, and right now I have 32571 range names.

Is there a way I can speed this macro up? I've been trying put the column into an array and somehow check that way but I'm having no luck.

Any help please!

Answer 1

The following code should be slightly better (if run in the context of Application.ScreenUpdating = Fasle, etc.):

Dim A As Variant
Set A = Range("RngeTbl").Value
For i = 1 To UBound(A)
    If A(i,2) <> "DO NOT CLEAR" Then Range(A(i,1)).Value = A(i,3)
Next i

If MaxRowCount is smaller than the number of rows in the range, then of course you could use that rather than UBound(A) in the loop.

Answer 2

This code will Sort your RngeTbl range on the "Clear?" column, then count how many non-Blank cells are in the "Clear?" column, and start the loop at the next row.

This will mean that the loop skips all of the "DO NOT CLEAR" ranges - if all ranges are to be cleared then the code will run slightly slower. If there are no ranges to be cleared then the code will only take about as long as the Sort does.

Dim lStart As Long

'Sort the range, without header
[RngeTbl].Sort [RngeTbl].Cells(1, 2), xlAscending, Header:=xlNo
'Since Calculation should be Manual for speed, we recalculate the sorted Range...
[RngeTbl].Calculate
'Count the Non-Blank cells in the "Clear?" column, to find the first non-blank cell
lStart = 1 + WorksheetFunction.CountA([RngTbl].Columns(2))

'If there ARE any non-blank cells
If lStart <= MaxRowCount Then
    'Skip all of the "DO NOT CLEAR" cells
    For i = lStart To MaxRowCount
        Range(Range("RngeTbl").Cells(i, 1).Value).Value = Range("RngeTbl").Cells(i, 3).Value
    Next i
Next lStart