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!

What is Range("Sheet1") good for? I think you want to refer to Worksheets("Sheet1"). Is that right? — Storax
– Storax, Commented Sep 27, 2018 at 10:38
Did you disable screen updating, etc.? Perhaps you can show the code that you tried but is giving you "no luck". Reading into an array seems like a reasonable choice. If you are modifying the contents of tens of thousands of named ranges, that could be problematic. If there is a scheme for the naming (surely they are not random) perhaps you could use that scheme and go directly to the cells. Without a minimal reproducible example it is hard to say more. — John Coleman
– John Coleman, Commented Sep 27, 2018 at 10:38
@JohnColeman They are not random, but there isnt a distinct scheme. I'm not sure how I can replicate the problem without posting the worksheet! I was hoping there would be a simple way to rewrite the code above (e.g. using arrays) which I can then use on the main spreadsheet. — user33484
– user33484, Commented Sep 27, 2018 at 10:41
This sounds like an XY problem. The names collection isn't really a good data structure to store tens of thousands of items, because of the constant dereferencing involved. If you can't refactor it, perhaps you could just once load all of the names into a dictionary of range objects and then use that dictionary in the main loop. — John Coleman
– John Coleman, Commented Sep 27, 2018 at 10:55

John Coleman · Accepted Answer · 2018-09-27 11:32:29Z

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.

edited Sep 27, 2018 at 11:32

answered Sep 27, 2018 at 11:19

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

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

3 Comments

user33484 Over a year ago

Thanks for your answer. Unfortunately the speed difference isn't that great! Perhaps there isn't a way around it.

John Coleman Over a year ago

If there is a rectangular range large enough to contain both RangleTbl and all of the cells referred to by those names,but not too large for VBA to directly handle, perhaps you could read that entire range into an array in 1 line of code, modify that array in memory, and then write it back.

Chronocidal Over a year ago

@user33484 You may be able to speed things up by either filtering the table to exclude "DO NOT CLEAR" and copying it to a new location, or by sorting it on the "Clear?" column and looping from the first Blank onwards, so that the only rows you look at are the ones that need clearing. If Application.Calculation = xlCalculationManual, then don't forget to .Calculate the new range manually before trying to use it.

Chronocidal · Accepted Answer · 2018-09-27 12:27:37Z

1

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

answered Sep 27, 2018 at 12:27

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

1 Comment

user33484 Over a year ago

Hi, Thanks for your answer. The issue with this is that blank cells will still need to be cleared; the proportion of "DO NOT CLEAR" cells is quite low, so it doesn't make too much of a difference to filter on them. I think a way to speed it up is to check if the current ranged name is the same as the "value if clear" column. If it is, then it does not need to clear it and can skip past it. I done this in VBA but it made little impact, I want to be able to check this in excel but indirect formula is very slow!

Collectives™ on Stack Overflow

VBA - Avoiding for loop

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related