如何使用 VBA 在 Excel 中自动填充单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2289218/
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 can I autofill cells in Excel using VBA?
提问by Lipis
I have some data in the first columns, some calculated fields right afterwards and I want to autofill the rest of the rows with the same rules that are in the first row.
我在第一列中有一些数据，之后有一些计算字段，我想使用与第一行相同的规则自动填充其余行。
The total number of rows, number of columns for input/calculated data are known and I would appreciate a working example for this data:
输入/计算数据的总行数、列数是已知的，我很感激这个数据的工作示例：
| A | B | C | D | E |
----------------------------------------------
1 | 3 | 1 | =A1+B1 | =A1*B1 | =sum(C1:D1)|
2 | 4 | 4 | | | |
3 | 5 | 23 | | | |
4 | 42 | 4 | | | |
5 | 7 | 4 | | | |
The real data usually has 10K+ rows and 30+ columns. When I'm trying to do it manually sometimes getting the error Selection is too large. I'm telling this because the general solution might not work using VBA either, but if I know how to autofill this example, I'll do it per column if necessary. Version of Excel is 2000 and it's not my fault :)
真实数据通常有 10K+ 行和 30+ 列。当我尝试手动执行时，有时会出现错误Selection is too large。我之所以这么说是因为使用 VBA 的通用解决方案也可能不起作用，但是如果我知道如何自动填充此示例，如有必要，我将按列执行此操作。Excel 的版本是 2000，这不是我的错:)
回答by Patrick Honorez
sub copydown()
Range("c1:e" & Range("a" & activesheet.rows.count).End(xlUp).Row).FillDown
end sub
回答by d..
Rudimentary, but it should give you something to build upon and it works in Excel 2003 (the oldest I have).
基本的，但它应该为您提供一些可以构建的东西，并且它可以在 Excel 2003（我拥有的最古老的）中运行。
Option Explicit
Public Sub CopyFormulaeExample()
On Error GoTo Handle_Exception
Dim lastRow As Long
Dim wrkSheet As Excel.Worksheet
'Book and sheet names hard-coded for this example
Set wrkSheet = Application.Workbooks("Book1").Worksheets("Sheet1")
'Get the index of the last row used
lastRow = wrkSheet.UsedRange.End(xlDown).Row
'Copy the cells containing the formulae; also hard-coded for this example
Range("C1:E1").Select
Selection.Copy
'Paste the selection to the range of interest
Range("C2:E" + CStr(lastRow)).PasteSpecial xlPasteAll
'Alternative approach
Range("C1:E1").Copy Range("C2:E" + CStr(lastRow))
'Release memory and exit method
Set wrkSheet = Nothing
Exit Sub
Handle_Exception:
Set wrkSheet = Nothing
MsgBox "An error has been found: " + Err.Description
End Sub
回答by Curtis Patrick
Using the copy down (ctrl-D) function.
使用向下复制 (ctrl-D) 功能。
Select Cells c1-e1 and then all the way down (if you have 10,000 rows of data, your selected cell range is c1-e10000).
选择单元格 c1-e1，然后一直向下（如果您有 10,000 行数据，则您选择的单元格范围是 c1-e10000）。
Press Ctrl-D.
按 Ctrl-D。
This copies the cell contents (your formulas) to all of the cells below it.
这会将单元格内容（您的公式）复制到它下面的所有单元格。
回答by Lakshman Prasad
Following is how I did it, when I did it :)
以下是我是如何做到的，当我这样做时:)
Ctrl+C
<--
Ctrl+Shift+Down
-->
Ctrl+Shift+Up
Ctrl+V
This seems to me to be the most efficient way. Nothing prevents you from wrapping this into a macro and assign a convenient key-binding.
这在我看来是最有效的方式。没有什么能阻止你把它包装成一个宏并分配一个方便的键绑定。
