[Solved] VBA: AutoFill using dynamic "Destination"

Relman · Sep 18th 2003

Please see the attached .xls

It uses the WorksheetChange() event to redimension a range [Data] as new items are added to the bottom of the list in column A, then AutoFill the formulas down column B.
As it stands, the code works fine, but I’d like to extend the AutoFill range to allow for additional columns C, D, E….etc.
[FillStart] is the range of cells from B1 to F1, and grows/shrinks as columns are added/deleted

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Fill_It_Down
End If
End Sub

Public Sub Fill_It_Down()

Set Data = Range([topoflist], [A65536].End(xlUp))
Set First = [topoflist].Offset(0, 1)
Set Fillstart = Range([First], [First].End(xlToRight))
Coli = [Fillstart].Columns.Count
Rowi = [Data].Rows.Count
If Rowi > 1 Then
[First].AutoFill Destination:=[Data].Offset(0, 1) < How to change this line???
End If

End Sub

How should I specify the Destination part to allow for an expanding [FillStart] range?

Chris

Varium et mutabile semper Excel

Pesky Weasel · Sep 18th 2003

Chris,

Not 100% sure without running the code but would changing

[First].AutoFill Destination:=[Data].Offset(0, 1)

to

[First].AutoFill Destination:=[Data].Offset(0, Coli)

do what you want?

Regards
Weasel

Relman · Sep 18th 2003

Hi Pesky, thanks for replying :wink2:

Alas, I've tried that, and it bombs out on that line with "AutoFill Method of Range class failed"

Same thing if I use both Rowi and Coli on the Offset().

Any other thoughts?

Chris

Richie(UK) · Sep 18th 2003

Hi Chris,

How about:

Code

Public Sub Fill_It_Down()
    Dim Data As Range, First As Range, Fillstart As Range
    Dim Coli As Integer, Rowi As Long
    
    Set Data = Range([TopOfList], [A65536].End(xlUp))
    Set First = [TopOfList].Offset(0, 1)
    Set Fillstart = Range([First], [First].End(xlToRight))
    
    Coli = [Fillstart].Columns.Count
    Rowi = [Data].Rows.Count
    
    If Rowi &gt; 1 Then
        [Fillstart].AutoFill Destination:=Range([Fillstart], [Fillstart].Offset(Rowi - 1))
    End If


End Sub

Display More

HTH

Relman · Sep 18th 2003

Brilliant Ritchie! :congrats:

The solution was right in front of my eyes;

Set Fillstart = Range([First], [First].End(xlToRight)) :duh:

Thanx M8!

Pesky Weasel · Sep 18th 2003

Hmmmmmmm!

Nice Once Richie - that works.

I cant see why mine wont though. The VBA shows that Coli is returning 5. I even tried
[First].AutoFill Destination:=[Data].Offset(0, [Fillstart].Columns.Count)

which is similar (but not exact) to an example in the VBA help files.
Any idea Richie????

Regards
Weasel

Richie(UK) · Sep 18th 2003

Hi Weasel,

OK, consider this:

1. From the VBE Help files on AutoFill - "The destination must include the source range"

2. Use a MessageBox (or your preferred method) to show the address of the Destination range that you suggest.

Does that help?

Also, we are looking to AutoFill a number of columns rather than just one so we would need to amend the [First] startpoint. How about:

[First].Resize(, Coli).AutoFill _
Destination:=Range([First].Resize(, Coli), [First].Offset(Rowi - 1).Resize(, Coli))

Probably NOT the easiest code in the world to understand ;;)

Pesky Weasel · Sep 18th 2003

Quote

Originally posted by Richie(UK)
Probably NOT the easiest code in the world to understand ;;)

Nope, i like yours better.

thx alot
Weasel

[Solved] VBA: AutoFill using dynamic "Destination"

Participate now!

Share