How can I autocomplete cells in Excel using VBA?

Question

I have some data in the first columns, some calculated fields right after that, and I want to autocomplete the rest of the lines with the same rules as in the first row.

The total number of rows, the number of columns for the input / calculated data is 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 | | | | 

Real data usually has 10K + rows and 30 + columns. When I try to do this manually, sometimes I get a Selection is too large error. I am talking about this because the general solution may not work with VBA, but if I know how to do this, then I will do it for each column, if necessary. The version of Excel is 2000, and this is not my mistake :)

Answer 1

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

Answer 2

It is rudimentary, but it should give you something to rely on, and it works in Excel 2003 (the oldest one 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 function (ctrl-D).

Select cells c1-e1, and then completely down (if you have 10,000 rows of data, the selected cell range is c1-e10000).

Press Ctrl-D.

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

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

Answer 4

This is how I did it when I did it :)

 Ctrl+C <-- Ctrl+Shift+Down --> Ctrl+Shift+Up Ctrl+V 

It seems to me that this is the most effective way. Nothing prevents you from wrapping this in a macro and assigning a convenient key binding.