Excel VBA UsedRange用于多个FOR循环，并“重置"操作.使宏更快 [英] Excel VBA UsedRange for multiple FOR loops, and "resetting" to make macro faster
问题描述
我已经开发了一个宏来清除复制/粘贴的数据集，这些数据只是Excel中具有多个列和标题的一系列行.为了帮助清理数据，我在其中添加了三个FOR LOOP流程:
I've developed a macro to cleanup sets of copy/pasted data that is just a series of rows into an Excel with multiple columns and headers. To help clean the data I've added three FOR LOOP processes to:
- 删除编号为项目符号的行
- 删除了多余的数据(英里和分钟有效)
- 手动将其他数据集剪切/粘贴到各自的列中(步骤& stepavg)
它似乎运行良好，但是我想优化该过程.在运行宏时，问题是我使用"UsedRange"来找出FOR LOOP 1有多少行(大约800行).在该循环中，删除了许多行，因此可以从800过滤到350.然后在执行FOR LOOP 2时，似乎UsedRange仍然引用800行，因此循环继续进行，从350过滤到65.最后使用FORLOOP 3，它处理并成功完成了所有65个操作，我可以说它完成了.但是，它将继续前进到第800行！
It seems to work well however I'd like to optimize the process. While running the macro, the issue is that I used "UsedRange" to figure out how many rows are present for FOR LOOP 1 (approx 800 rows). During that loops, many rows are deleted so it may filter from 800 to 350. Then when FOR LOOP 2 is performed, it seems like UsedRange is still reference 800 rows so the loop just continues, which filters from 350 to 65. Finally with FOR LOOP 3, it crunches and completes all 65 successfully and I can tell that its done. BUT, it'll keep going to row 800!
是否有任何建议清除"或重置" UsedRange，以便此过程更快?除了这个问题，我的宏效果很好.
Any suggestions to "clear" or "reset" the UsedRange so this process is faster? Other than this issue, my macro works great.
'Cleaning the Data
Dim i As Long
Dim j As Long
Dim k As Long
Dim maxRow As Long
maxRow = ActiveSheet.UsedRange.Rows.Count
'Removes all those single number rows
For i = 2 To maxRow Step 3
Rows(i).Select
Selection.Delete Shift:=xlLeft
Next i
Range("A1").Select
'Removes all those miles and min active data
Dim maxRow2 As Long
maxRow2 = ActiveSheet.UsedRange.Rows.Count
For j = 5 To maxRow2 Step 3
Range(Rows(j), Rows(j + 5)).Select
Selection.Delete Shift:=x1Up
Next j
Range("A1").Select
'Cut/paste the Steps and StepsAvg data
Dim maxRow3 As Long
maxRow3 = ActiveSheet.UsedRange.Rows.Count
For k = 3 To maxRow Step 1
Cells(k, 1).Select
Selection.Cut
Cells(k - 1, 2).Select
ActiveSheet.Paste
Cells(k + 1, 1).Select
Selection.Cut
Cells(k - 1, 3).Select
ActiveSheet.Paste
Range(Rows(k), Rows(k + 1)).Select
Selection.Delete Shift:=x1Up
Next k
推荐答案
首先，这里有一些指针:
For a start here are a few pointers:
1)不要使用变量，除非要多次使用它. maxRow ， maxRow2 和 maxRow3 是毫无意义的.每个新变量都会增加您的应用程序正在使用的内存(RAM)数量.在某些时候，VBA的垃圾收集器"将会出现并进一步降低您的代码速度.出于这个原因，使用大量变量是不好的做法，但是减少这些不必要的变量的数量也可以提高可读性并减少混乱.
1) Don't use a variable unless you are going to use it more than once. maxRow, maxRow2 and maxRow3 are pointless. Each new variable increases the amount of memory (RAM) your application is using. At some point the VBA 'garbage collector' will come along and slow your code even more. Using numerous variables is bad practice for this reason, but reducing the number of these unnecessary variables also improves readability and reduces clutter.
2)定义循环变量时，无需为每个循环定义3个单独的变量.始终使用相同的内容.使用多个循环变量的唯一原因是使用嵌套循环.例如
2) When defining a loop variable there is no need to define 3 separate variables for each loop. Use the same one throughout. The only reason to use more than 1 loop variable is if you are using nested loops. E.G.
for i = 1 to 10
for j = 2 to 5
for k = 3 to 7
debug.print i & "," & j & "," & k
next k
next j
next i
3)您不需要选择一个单元来对其进行操作.以下语句是等效的:
3) You don't need to select a cell to manipulate it. The following statements are equivalent:
cells(1,1).select
selection.value = "hello"
v.s.
cells(1,1).value = "hello"
尽管在这种情况下速度可以忽略不计，但是在循环遍历一个区域时，直接设置值比选择单元格然后设置值要快得多.
Although in this case the speed is negligible, while looping over a region, setting the value directly is much faster than selecting the cell and then setting the value.
4)要将值从一个单元格传输到另一个单元格，无需使用剪切和粘贴:
4) To transfer a value from one cell to another you do not need to use Cut and Paste:
cells(1,1).cut
cells(2,1).select
activesheet.paste
(几乎)与
cells(2,1).value = cells(1,1).value 'doesn't copy formatting or formula! I assume this isn't required.
5)删除整行数据时，不需要进行移位(除非您要向下移位数据).因此，可以将 rows(i).delete Shift:= x1Up 简化为 rows(i).delete
把所有东西都放在一起就可以得到，我认为这更具可读性，而且速度也快得多:
Throwing all that together you get this, which in my opinion is a lot more readable and is also a lot faster:
Dim i As Long
'Removes all those single number rows
For i = 2 To ActiveSheet.UsedRange.Rows.Count Step 3
Rows(i).Delete
Next i
'Removes all those miles and min active data
For i = 5 To ActiveSheet.UsedRange.Rows.Count Step 3
Range(Rows(i), Rows(i + 5)).Delete
Next i
'Cut/paste the Steps and StepsAvg data
For i = 3 To ActiveSheet.UsedRange.Rows.Count Step 1
Cells(i - 1, 2).Value = Cells(i, 1).Value
Cells(i - 1, 3).Value = Cells(i + 1, 1).Value
Range(Rows(i), Rows(i + 1)).Delete
Next i
要提高速度，还有很多事情可以做.例如， Application.ScreenUpdating = false 和 Application.EnableEvents = False .更复杂的效率包括批量删除行，而不是逐行删除行.例如
There is still a lot that can be done to improve the speed. For example Application.ScreenUpdating=false and Application.EnableEvents=False. More complex efficiencies include deleting rows in bulk instead of row by row. E.G.
'Removes all those single number rows
Dim rng as range: set rng = Rows(2)
For i = 5 To ActiveSheet.UsedRange.Rows.Count Step 3
set rng = Application.union(rng,Rows(i))
Next i
rng.Delete
尽管代码看起来更复杂，但是它也更快，因为您要批量处理更多行.另一种选择是使用数组而不是范围，这可能是最快的方法，但要复杂得多.
Although the code looks more complex it is also faster because you are doing more rows in bulk. Another alternative would be to use array instead of ranges which would likely be the fastest method, but would be vastly more complex.
dim myArray as variant: myArray = Activesheet.UsedRange.Value
'do stuff with array
ActiveSheet.Clear
ActiveSheet.range(cells(1,1),cells(ubound(myArray,1),ubound(myArray,2)).value = myArray
但是我离题，前者可能对您来说足够快了.
But I digress the former is likely fast enough for you as is.
这篇关于Excel VBA UsedRange用于多个FOR循环，并“重置"操作.使宏更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
