Excel VBA to run AutoFill on a Dynamic Range

When recording a macro to AutoFill all the cells, the recording specific a static range.  I need to make this range dynamic.  For instance, in one run my AutoFill range may need to be from C13:C30, while the next run could be from C13:C37.  I took a shot at this and failed.  Any ideas?  My failed code is below.  Thanks for any help!
Range("C13").Select
    'Selection.AutoFill Destination:=Range("C13:C30")
    Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown)).Select

Open in new window

KP_SoCalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
How about:

    Range("C13:c30").Select
    Selection.FillDown

or if you select that range beforehand then simply selection.filldown

Chris
0
Chris BottomleySoftware Quality Lead EngineerCommented:
ALternatively if the startcell is fixed then:

C1 to the first selected cell
    Range("c1:" & Selection.Cells(1).Address).Select
    Selection.FillDown
or
C1 to the first selected cell
    Range("c1:" & Selection.Cells(Selection.Cells.Count).Address).Select
    Selection.FillDown

Chris
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Second one in the last post is c1 to the last selected cell ... the perils of cut and paste!

Chris
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

KP_SoCalAuthor Commented:
Hi Chris, thanks for the quick reply. Unfortunately these suggestions aren't working for me. When I select C13, I want to it AutoFill down to the last populated cell.

So if my last populated cell was C30 and I was recording the marco, the code would looking like this:

Sub Macro1
Range("C13").Select
Selection.AutoFill Destination:=Range("C13:C30")
End Sub

However, I may paste new data in that still starts at C13 but extends down to C52.  So I would need to do another AutoFill to copy my formulas and formats from C13.  The new recorded macro would look something like this...
Sub Macro2
Range("C13").Select
Selection.AutoFill Destination:=Range("C13:C52")
End Sub

So I guess Selection.AutoFill Destination:=Range("C13:C52") is the link of code that needs to be dynamic.

 
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Sounds like a worksheet macro as follows to me:

CHris
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range

    Set rng = Intersect(Target, Range("c13"))
    If rng Is Nothing Then Exit Sub
    Range(Range("c13"), Range("c13").End(xlDown)).FillDown
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris BottomleySoftware Quality Lead EngineerCommented:
For worksheet event handlers:
     In the project tree select as appropriate:
        EXCEL      : the worksheet code page
     In the workpane select as appropriate:
        EXCEL      : WorkSheet
     In the workpane select the required 'event', (i.e. 'Change')

0
KP_SoCalAuthor Commented:
Chris, thanks for pointing me in the right direction on this. I was able to tailor your suggestions into what I need (see below).


Sub Testing()
Dim rngAutoFill As Range
Set rngAutoFill = Range("C13")
Range(rngAutoFill, rngAutoFill.End(xlDown)).FillDown
End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.