VBA-避免循环 [英] VBA - Avoiding for loop

问题描述

我在excel工作表中分别在A，B和C列中包含以下数据.

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 |
+-----------+--------------+----------------+

有一个清除"宏，用于检查每个excel范围名称，如果B列显示请勿清除"，则它将跳过并且不执行任何操作，如果为空，则它将清除范围名称并设置范围名称值添加到C列.代码如下:

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

但是，范围名称的数量正在急剧增加，现在我有32571个范围名称.

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.

请帮忙！

推荐答案

此代码将在清除?"上对您的 RngeTbl 范围进行排序.列，然后计算清除"中有多少个非空白单元格?列，然后从下一行开始循环.

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.

这意味着循环将跳过所有请勿清除"范围-如果要清除所有所有范围，则代码将运行得稍微慢一些.如果没有要清除的 范围，则该代码仅需要与 Sort 一样长的时间.

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

这篇关于VBA-避免循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！