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