vba 如何使用另一列的行数自动填充一列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17284973/
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
How do I AutoFill down in one column, using the row count of another column?
提问by Econ502
I am trying to produce some code that will use the AutoFill Down function. I would like to fill Column B with the typical 1,2,3,4, etc. as long as there is text/values in the respective rows of Column A. Right now, my code is hardwired to fill down to cell B50 no matter what, but I don't want it to paste that far down if Column A only has data through cell A7, for example. Ideally, I would like to use the following variable -- rownum = Range("A1").End(xlDown).Row -- to count the number of cells in Column A that have text/values, and use that to replace "B50" in the row designation below. Just not sure of the appropriate syntax to make that happen. Here is the code I have so far.
我正在尝试生成一些将使用 AutoFill Down 功能的代码。我想用典型的 1、2、3、4 等填充 B 列，只要 A 列的相应行中有文本/值。现在，我的代码被硬连线填充到单元格 B50 没有无论如何，但我不希望它粘贴那么远，例如，如果 A 列只有通过单元格 A7 的数据。理想情况下，我想使用以下变量 -- rownum = Range("A1").End(xlDown).Row -- 来计算 A 列中具有文本/值的单元格数量，并用它来替换“ B50”在下面的行名称中。只是不确定实现这一点的适当语法。这是我到目前为止的代码。
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("A1").Select
Range("B1:B2").Select
Selection.AutoFill Destination:=Range("B1:B50"), Type:=xlFillDefault
Thanks in advance to anyone who helps me out! I am a new user of both Macros and VBA Code, and the amount of knowledge that so many of you have amazes me!
在此先感谢任何帮助我的人！我是宏和 VBA 代码的新用户，你们这么多人拥有的知识量让我惊讶！
Econ
经济
回答by Jon Crowell
You're much better off if you don't select anything in your code. If you just want to number the rows in column B based on the contents of column A, this will do the trick:
如果您不在代码中选择任何内容，情况会好得多。如果您只想根据 A 列的内容对 B 列中的行进行编号，则可以这样做：
Sub NumberColumn()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
' get the last row from column A that has a value
lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
' use the last row to determine how far down to extend the formula
ws.Range("B1:B" & lastRow).Formula = "=row()"
End Sub
回答by Frank
Pretty simple option to use without the need for a string:
无需字符串即可使用的非常简单的选项：
Selection.AutoFill Destination:=Range("A2:A" & Cells(Rows.Count, "B").End(xlUp).Row)
Same goes for copying the number of rows in a column:
复制列中的行数也是如此：
Range("A2:A" & Cells(Rows.Count, "B").End(xlUp).Row).Select
Selection.Copy
