vba 自动填充动态范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14161637/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Autofill with a dynamic range
提问by Dm3k1
I'm constructing a program to automate some work, using record and then cleaning. It has worked well up until a calculation that takes too long for me. Its a simple nested IF statement
我正在构建一个程序来自动化一些工作，使用记录然后清理。它一直运行良好，直到计算对我来说太长了。它是一个简单的嵌套 IF 语句
ActiveCell.FormulaR1C1 = _
"=IF(RC[-16]="""",""MISSING"",IF(RC[-14]="""",""MISSING"",RC[-14]-RC[-16]))"
We deal with data that can range from being only 10 rows up to a couple hundred thousand. My "solution" that I'm not happy with so far has limited the autofill to range A1:A35000 - which still takes Excel a bit to process. This was the solution to avoid xlDowntaking me to the 1 millionth row. Further, I've tried reducing sheet size, that works as well but is not a good solution.
我们处理的数据范围从只有 10 行到几十万行。到目前为止，我不满意的“解决方案”将自动填充限制在 A1:A35000 范围内 - 这仍然需要 Excel 来处理。这是避免xlDown将我带到第 100 万行的解决方案。此外，我尝试减小纸张大小，这也有效，但不是一个好的解决方案。
This is what the code looks like:
这是代码的样子：
Selection.AutoFill Destination:=ActiveCell.Range("A1:A35000"), Type:= _
xlFillDefault
What I want to do is to either:
我想做的是：
autofill from a range referenced by a number in a given cell (so if the data i put is 500 rows I have a cell I type in 500 and all the autofills go from A1:A500), or
more preferably, this would be done automatically by having the program already recognize the range to autofill.
从给定单元格中的数字引用的范围自动填充（因此，如果我输入的数据是 500 行，我有一个单元格，我输入 500 并且所有自动填充都从 A1:A500 开始），或
更优选地，这将通过让程序已经识别要自动填充的范围来自动完成。
I've checked through the solutions and can't figure out how to apply it to my situation.
我已经检查了解决方案，但无法弄清楚如何将其应用于我的情况。
回答by Katy
I think you may be looking for the following ...
我想你可能正在寻找以下...
Dim ws as Worksheet
Set ws = Worksheets("Sheet1")
Dim usedRows as Long
'Option One (not recommended): USED RANGE METHOD
usedRows = ws.UsedRange.Rows.Count
'Option Two (more robust) .END(xlUp) METHOD (Assuming you have your Data in column "RC")
usedRows = ws.Cells(ws.Rows.Count, "RC").End(xlUp).Row
'YOUR
'CODE
'HERE
Selection.AutoFill Destination:=ws.Range(Cells(1,1),Cells(usedRows,1)), Type:= _ xlFillDefault
if that column has the most used rows of any in your worksheet.
如果该列具有工作表中最常用的行。
Thanks to @scott for pointing out the .End(xlUp) option's superiority :)
感谢@scott 指出 .End(xlUp) 选项的优越性:)
