How can I autofill cells in Excel using VBA?
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. Versio开发者_如何学Gon of Excel is 2000 and it's not my fault :)
sub copydown()
Range("c1:e" & Range("a" & activesheet.rows.count).End(xlUp).Row).FillDown
end sub
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
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
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.
精彩评论