• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1177
  • Last Modified:

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

0
KP_SoCal
Asked:
KP_SoCal
  • 5
  • 2
1 Solution
 
Chris BottomleyCommented:
How about:

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

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

Chris
0
 
Chris BottomleyCommented:
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 BottomleyCommented:
Second one in the last post is c1 to the last selected cell ... the perils of cut and paste!

Chris
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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 BottomleyCommented:
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
 
Chris BottomleyCommented:
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now