自动填充动态范围 [英] Autofill with a dynamic range

问题描述

我不是一个非常有经验的VBA程序员，我非常了解基础知识。现在我正在构建一个程序来自动化一些工作，使用记录然后清理。它已经很好了，直到对我来说需要太长时间的计算。它是一个简单的嵌套IF语句

ActiveCell.FormulaR1C1 = _
= IF（RC [-16] = 缺少 ，IF（RC [-14] = ， 丢失 ，RC [-14] -RC [-16]））

我们处理的数据范围可能只有10行到几十万。到目前为止，我不满意的解决方案限制了A1：A35000的自动填充，这仍然需要处理。这是解决方案，以避免xldown带我到百万分之一排。此外，我已经尝试减少工作表大小，这也是一个很好的解决方案。

这是代码看起来像
Selection.AutoFill目的地：= ActiveCell.Range（A1：A35000），类型：= _
xlFillDefault

我想做的是A）从给定单元格中的数字引用的范围自动填充（所以如果我输入的数据是500行，我有一个单元格，我输入500，所有的自动填充从A1开始：A500 ）更优选地，这将通过使程序已经识别到自动填充的范围来自动完成。

我已经检查了解决方案，无法弄清楚如何将其应用于我的情况。

感谢您提供任何帮助/支持。

解决方案

我想你可能正在寻找以下内容...

  Dim ws as Worksheet 
设置ws = Worksheets Sheet1）
 
 Dim usedRows as Long 
 
'选项一（不推荐）：USED RANGE METHOD 
 usedRows = ws.UsedRange.Rows.Count 
 
'选项二（更健壮）.END（xlUp）方法（假设您的数据在RC列中）
 usedRows = ws.Cells（ws.Rows.Count，RC ）.End（xlUp）.Row 
 
'你的
'CODE 
'HERE 
 
 Selection.AutoFill目的地：= ws.Range（Cells 1,1），Cells（usedRows，1）），Type：= _ xlFillDefault 
  

在您的工作表中有最常用的行。

感谢@scott指出.End（xlUp）选项的优势:)

I'm not a very experienced VBA programmer, I know the very basics. Right now 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

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 xldown taking me to the 1 millionth row. Further, I've tried reducing sheet size, that works as well but is not a good solution.

This is what the code looks like Selection.AutoFill Destination:=ActiveCell.Range("A1:A35000"), Type:= _ xlFillDefault

What I want to do is to either A) 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)

B) more preferably, this would be done automatically by having the program already recognize the range to autofill.

I've checked through the solutions and can't figure out how to apply it to my situation.

Thank you for any help/support you can offer.

解决方案

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 :)

这篇关于自动填充动态范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！