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

Patrick Honorez · Accepted Answer · 2015-02-05 14:29:49Z

6

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

edited Feb 5, 2015 at 14:29

answered Feb 18, 2010 at 16:22

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lipis Over a year ago

This looks promising but I have to try at work tomorrow to make sure.

Our Man in Bananas Over a year ago

in versions after Excel 2003, there can be upwards of a million possible rows in a worksheet, so maybe Range("c1:e" & Range("a" & activesheet.rows.count).End(xlUp).Row).FillDown

Patrick Honorez Over a year ago

@Philip: better indeed. +1

d.. · Accepted Answer · 2010-02-18 15:13:10Z

1

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

answered Feb 18, 2010 at 15:13

d..

1,1031 gold badge10 silver badges19 bronze badges

3 Comments

Lipis Over a year ago

This also looks very nice and I'll try it as well.

d.. Over a year ago

No problem whatsoever. Beware of the hard-coded "a65000" though; I could be wrong but I think this won't work in all cases. In 2003 the max number of rows is 65,536 (not 65,000) and, in 2007, over a million.

Lipis Over a year ago

Thanks d. I know where is my last row so I don't have to worry about it. At the end I had to use a for loop for columns cause on the actual document Ì was getting the Selection is too large.

Curtis Patrick · Accepted Answer · 2010-02-18 15:23:36Z

1

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

answered Feb 18, 2010 at 15:23

Curtis Patrick

978 bronze badges

1 Comment

Curtis Patrick Over a year ago

Your original post didn't state how you were doing it manually, so I tried to provide you an alternative solution that would not require you to include VBA code with each revision on your document. Sorry my suggestion wasn't helpful.

lprsd · Accepted Answer · 2010-02-18 14:46:30Z

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

answered Feb 18, 2010 at 14:46

lprsd

87.9k48 gold badges140 silver badges169 bronze badges

1 Comment

Lipis Over a year ago

I know how to solve the problem with many different ways.. but usually it's not me who has to deal with these files. I want a general solution and a working example using VBA code for the above input data. Using hardcoded values for rows/columns.

Collectives™ on Stack Overflow

How can I autofill cells in Excel using VBA?

4 Answers 4

3 Comments

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related