如何使用VBA在Excel中自动填充单元格?

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+列。当我尝试手动操作时，有时会发现错误选择太大。之所以这么说，是因为一般的解决方案可能也不能使用VBA，但是如果我知道如何自动填充这个例子，如果需要的话，我会在每一列都这样做。Excel的版本是2000，这不是我的错

4 个解决方案

#1

sub copydown()
    Range("c1:e" & Range("a" & activesheet.rows.count).End(xlUp).Row).FillDown
end sub

#2

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

#3

Using the copy down (ctrl-D) function.

使用copy down (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).

选择Cells c1-e1，然后一直向下(如果有10,000行数据，则选择的cell范围是c1-e10000)。

Press Ctrl-D.

按下ctrl - d。

This copies the cell contents (your formulas) to all of the cells below it.

这将单元格内容(您的公式)复制到它下面的所有单元格。

http://www.google.com/search?q=using+excel+ctrl-d

http://www.google.com/search?q=using + excel + ctrl - d

#4

-2

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.

在我看来，这是最有效的方法。没有什么可以阻止您将其封装到一个宏中并分配一个方便的键绑定。

#1

sub copydown()
    Range("c1:e" & Range("a" & activesheet.rows.count).End(xlUp).Row).FillDown
end sub

#2