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.
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
“then double-click the fill handle,”
Rob, you obviously haven’t heard of my mouse-a-phobia :)
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
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
” 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
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.
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.
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
1 1632497 3629842409838
2 3742803
3 4972902
4 6353011
5 7303046
6 8563079
7 9633087
8 11233091
9 13733398
10 16383729
11 18583990
12 20834248
13 24434524
14 28034800
15 31635077
16 35235337
17 37535320
18 40005299
19 1382449362829 2409363
20 1882532
21 3812808
22 5002904
23 6453013
24 8633077
25 9883082
26 11233088
27 13733396
28 16363727
29 18363962
30 20384196
31 23984481
32 27584755
33 31185030
34 35045319
35 37445309
36 40005288
37 1382442362675 2408887
38 1882525
39 3812803
40 5002902
41 6733018
42 8493070
43 9883077
44 11233083
45 13733393
46 16363728
47 18363961
48 20384192
49 23984473
50 27584745
51 31185018
52 35045306
53 37445298
54 40005278