Autofill with variable (dynamic) range VBA

[jmking02]

Hi,

I have a problem with the Autofill. I want to have and autofill that is dynamic because i need to insert ans delete column.

Here.s the basic autofill:

Range("AM2:AN12").Select
Selection.AutoFill Destination:=Range("AM2:AN100"), Type:=xlFillDefault

Here's what I try to have a autofill that autofill me the last column but it does not work... :

Sub Last_Real_Populated_Column()
 
 
 ActiveCell.SpecialCells(xlLastCell).Select
 LastR = ActiveCell.Row
 LastC = ActiveCell.Column
 LastRealC = 1
 For Counter = LastC To 1 Step -1
    Range(Cells(LastR, Counter), Cells(LastR, Counter)).Select
     Selection.End(xlUp).Select
    If Not IsEmpty(ActiveCell.Value) Then
        LastRealC = ActiveCell.Column
         Exit For
    End If
Next
Selection.End(xlUp).Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
[COLOR=red]Selection.Name = "dest"[/COLOR]
 
[COLOR=red]Selection.AutoFill Destination:=Range(dest), Type:=xlFillDefault[/COLOR]

Thanks for your help!

J-M

 

[jmking02]

Here's my solution

Sub Last_Real_Populated_Column()
 
 
 ActiveCell.SpecialCells(xlLastCell).Select
 LastR = ActiveCell.Row
 LastC = ActiveCell.Column
 LastRealC = 1
 For Counter = LastC To 1 Step -1
    Range(Cells(LastR, Counter), Cells(LastR, Counter)).Select
     Selection.End(xlUp).Select
    If Not IsEmpty(ActiveCell.Value) Then
        LastRealC = ActiveCell.Column
         Exit For
    End If
Next
Selection.End(xlUp).Offset(1, 0).Select
 
ActiveCell.Select
debut = ActiveCell.Address
fin = ActiveCell.Offset(200, 0).Address
Range(debut, fin).Select
 
ActiveCell.Select
Selection.AutoFill Destination:=Range(debut, fin), Type:=xlFillDefault
 
ActiveCell.Offset(0, -1).Select
debut = ActiveCell.Address
fin = ActiveCell.Offset(200, 0).Address
Range(debut, fin).Select
 
ActiveCell.Select
Selection.AutoFill Destination:=Range(debut, fin), Type:=xlFillDefault

 

[venkat1926]

you need not select everything. my macro is more general. It asks you the first cell,lastcell, firstcell value and step and fill it up. it is dynamic in the sense you give all the parameters .

Sub testone()
Dim lastc As String, j, firstc As String, sstep
'lastc is lastcell
 firstc = InputBox("type firsts cell e.g. A1")
j = InputBox("type data you want to enter in firstcell, d.g. 1 or 3 etc")
lastc = InputBox("type the upto lastcell you want to auttofil,e.g.A10")
sstep = InputBox("type the step e.g. 1 or 2 or 3 as you like")
Range(firstc) = j
Range(firstc, lastc).DataSeries Rowcol:=xlColumns, Type:=xlLinear, step:=sstep, Trend:=False
End Sub

try this in an empty sheet