AutoFill Macro
I've recently added a new macro to my Personal.xls (that's four now!). This one is to replace the cumbersome Edit > Fill > Series > Autofill (alt-e-i-s, alt-f, enter).
If TypeName(Selection) = "Range" Then
Selection.DataSeries , xlAutoFill
End If
End Sub
This uses the DataSeries method of the Range object. Honestly, I expected there to be a FillAuto method, but it turns out it's called AutoFill. That really is a better name, so I don't know what I was thinking. I was probably thinking along the lines of FillDown and FillRight. I'm not sure the AutoFill method would work in this capacity, however, because I wouldn't know on which Range to perform the AutoFill. For instance, if I had the numbers 1 through 7 in A1:A7, I select A1:A10 and run the above macro to fill the series down to get 1 through 10. The equivalent AutoFill would look like
I can replace Range("A1:A10") with Selection, but I don't know how to replace Range("A1:A7"). Maybe that's why they have two methods for this. It works for me, and that's what's important. Here's a rundown of the arguments for the DataSeries method:
Rowcol: You can specify whether to fill by rows (xlRows) or columns (xlColumns). I've never changed Excel's guess in the user interface, so I didn't see the need to include my own logic in this macro. I omit the argument and take Excel's guess.
Type: This corresponds to the four option buttons on the Fill Series Dialog; Linear, Growth, Date, AutoFill. Linear is the default, which was surprising to me. I thought AutoFill would be. I'm not sure I understand what these mean, but my best guess is that AutoFill determines the proper type of fill based on the data that's already selected. That works for me most of the time.
And the rest: The Professor and Mary Ann of the DataSeries arguments. You can determine how to increment the series, when to stop it, and whether to create a trend. I always enter the first two cells which determines the increment, I stop selecting when I want the series to stop, and I leave the trend setting to Old Navy.
Rob van Gelder:
Dick,
Very useful.
You probably already know this, but...
If you have A1:A10 containing 123.
then type A1 = 1, A2 = 2.
select A1:A2 then double-click the fill handle, it fills down in the column region.
Rob
18 October 2005, 1:46 pmDick Kusleika:
"then double-click the fill handle,"
Rob, you obviously haven't heard of my mouse-a-phobia
18 October 2005, 3:13 pmDavid Wasserman:
Dick,
This macro will take the first two items and autofill down or across depending on the selection. Note that the selection must be in one row OR one column.
Sub FillAuto()
If Selection.Columns.Count = 1 And Selection.Cells.Count > 1 Then
Selection.Range(Cells(1, 1), Cells(2,1)).AutoFill Selection
ElseIf Selection.Rows.Count = 1 And Selection.Cells.Count > 1 Then
Selection.Range(Cells(1, 1), Cells(1,2)).AutoFill Selection
End If
End Sub
HTH...
David
18 October 2005, 5:11 pmJim Rech:
I too added a fill handle macro to my personal (that makes 954) but I wanted it to work for blocks and even multiple areas. Of course I've never actually needed to do more than one column or row at a time but maybe someday.
Jim
''An attempt to do what dragging the Fill Handle does
''To be attached to a keyboard shortcut (Ctrl-Shift-h) so
''I do not have to drag the file handle which is awkward
''for large ranges.
''Works with multiple area selections, mixed columns
'' and rows, treating each column and row separately
Sub FillHandleSubst()
Dim CurrArea As Range, StepVal As Double
Dim CurrSlice As Range
For Each CurrArea In Selection.Areas
If RangeIsFilled(CurrArea.Columns(1)) Then
''If entire first col is filled we're filled across rows
For Each CurrSlice In CurrArea.Rows ''Do each row separately
StepVal = GetStepVal(CurrSlice)
CurrSlice.DataSeries Rowcol:=xlRows, Step:=StepVal
Next
Else
''Since first col is not filled, fill down columns individually
For Each CurrSlice In CurrArea.Columns
StepVal = GetStepVal(CurrSlice)
CurrSlice.DataSeries Rowcol:=xlColumns, Step:=StepVal
Next
End If
Next
End Sub
''Returns True if entire passed range is filled
Function RangeIsFilled(Rg As Range) As Boolean
On Error Resume Next
RangeIsFilled = (Application.CountA(Rg) = Rg.Cells.Count)
End Function
''The difference between the first and second cells in the
19 October 2005, 9:33 am'' passed row or column is the step amount.
''But if the second cell is empty make the step 1.
Function GetStepVal(Rg As Range) As Double
Dim Cell2Val As Variant
Cell2Val = Rg.Cells(2).Value
If Cell2Val = "" Then
GetStepVal = 1
Else
GetStepVal = Cell2Val - Rg.Cells(1)
End If
End Function
mike:
hi
i have data that changes regulary. it wrote a macro to import into excel. after it has been imported, not all cells in each column has data in it. what i need is to autofill the cell in a column until just before a change in field and then it must autofill the new field. until the next and so on.
please assist.
25 November 2005, 6:55 amAkash Rao:
Dim jmp As Integer
For i = 1 To 172
jmp = 1
If Not IsNumeric(Range("A1")) Then
'MsgBox ("Value of Cell is Alpabetic" + Range("A1"))
Range("&value(jmp) &:& value(jmp)&").Select
Range("1:1").Select
Selection.Delete Shift:=xlUp
'Rows("9:9").Select
'Selection.Delete Shift:=xlUp
Else
jmp = jmp + 1
End If
Next
End Sub
Can i use some varibles in place of the varible JMP.
6 June 2007, 7:56 amArshad:
Hello Members,
I have a question for how to create a macro in Excel for below problem.
I have a sequence of data in Excel below mentioned and I want to autofill the the C and D columns with the similar C1 and D1 Autofill down to C18 and D18 then start selection again from C19 and D19 to C36 and D36 Autofill and goes on like this till Last Rows 65534.
Would Appreciate if anyone can help me in this.
Arshad
A B C D
3 March 2008, 2:57 am1 163 2497 362984 2409838
2 374 2803
3 497 2902
4 635 3011
5 730 3046
6 856 3079
7 963 3087
8 1123 3091
9 1373 3398
10 1638 3729
11 1858 3990
12 2083 4248
13 2443 4524
14 2803 4800
15 3163 5077
16 3523 5337
17 3753 5320
18 4000 5299
19 138 2449 362829 2409363
20 188 2532
21 381 2808
22 500 2904
23 645 3013
24 863 3077
25 988 3082
26 1123 3088
27 1373 3396
28 1636 3727
29 1836 3962
30 2038 4196
31 2398 4481
32 2758 4755
33 3118 5030
34 3504 5319
35 3744 5309
36 4000 5288
37 138 2442 362675 2408887
38 188 2525
39 381 2803
40 500 2902
41 673 3018
42 849 3070
43 988 3077
44 1123 3083
45 1373 3393
46 1636 3728
47 1836 3961
48 2038 4192
49 2398 4473
50 2758 4745
51 3118 5018
52 3504 5306
53 3744 5298
54 4000 5278