How can I autofill cells in Excel using VBA?

Question

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

Answer 1

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.

Answer 2

Rudimentary, but it should give you something to build upon and it works in Excel 2003 (the oldest I have).

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

Answer 3

Using the copy down (ctrl-D) function.

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

Press 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

Answer 4

sub copydown()
    Range("c1:e" & Range("a65000").End(xlUp).Row).FillDown
end sub