FillDown user selected cells to last used row in Column A

[jsauber]

The code I have below works great if I am only selecting one cell. I need the sub to work on multiple cells at once. The current code allows me to select multiple cells but only does FillDown on the first selected cell. I have searched for a solution but have yet to find one. All solutions that I have found only work if the columns are always the same. My need is for a dynamic macro that works regardless of the cells I select. Can someone please help.

Kind Regards,
jsauber

Sub copy_down()

    Application.InputBox("Select Range", "Fill Down", Type:=8).Select
    Range(ActiveCell.Address & ":" & Cells(Cells(Rows.Count, "A").End(xlUp).Row, ActiveCell.Column).Address).FillDown


    
End Sub

 

[Gingertrees]

Try this:

Sub Test2()
Dim rng As Range
Dim TR&, BR&, LC%, RC%, BottomRow%
Set rng = Application.InputBox("Select Range", "Fill Down", Type:=8)
rng.Select
With Selection
TR = .Row
BR = .Rows.Count + .Row - 1
LC = .Column
RC = .Columns.Count + .Column - 1
BottomRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
'MsgBox _
'"Top row: " & TR & vbCrLf & _
'"Bottom row: " & BR & vbCrLf & _
'"Left Column: " & LC & vbCrLf & _
'"Right column: " & RC
End With
Range(Cells(TR, LC), Cells(TR, RC)).Select 'top row cells only
Selection.AutoFill Destination:=rng
'//OR
'Selection.AutoFill Destination:=Range(Cells(TR, LC), Cells(BottomRow, LC))


'//first Autofill is if you've selected e.g. B2:F10, and you want the values in row 2
'//to autofill down to row 10. Now, if you want it to autofill down to the
'//last row in the sheet, you'd use the second Autofill and comment out the other:
'/-/-/-/-/Selection.Autofill Destination:=Range(Cells(TR,LC),Cells(BottomRow,LC))


Range("A1").Select


End Sub

WARNING: my commented-out alternative, to [in theory] fill-down to the last row of the sheet, doesn't work. I don't know why. But the main one does.

NOTE: code innovated from this post:
http://www.mrexcel.com/forum/excel-...ns-identify-top-left-cell-selected-range.html

 

[jsauber]

Thank you for looking into this. I am able to get the code to run up until

Selection.AutoFill Destination:=rng

The macro stops and I am getting a run-time 1004 error
AutoFill method of Range class failed
This occurs for both autofill options.

I filled column A with numbers down to row 20 and then put formulas in C1 and D1 and was trying to Fill them down.
Maybe I have something goofed in my setup.

 

[MARK858]

Try...

Sub xxx()
    Dim x As Long, lr As Long, y As Range
    x = Selection.Columns.Count
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    Set y = Range(Selection.Cells(1, 1), Selection.Cells(1, 1).Offset(x))
    Selection.AutoFill y.Resize(lr - (Selection.Cells(1, 1).Row - 1), x)
End Sub

you can put the inputbox back in once you are sure the selection and autofill part work

 

[jsauber]

Mark858 you are a genius! I am still a newbie with VBA so I am clueless what I can replace in your code with the inputbox line. This is ok though because your script will work great as it is. I will be the only one using it so I will know to select the cells I want prior to calling the macro. Thank you so much for your help. If you get bored the ability to select nonadjacent cells would be a nice addition. Thanks again to both you and Gingertrees for taking the time to look into this. I am very appreciative of your help.

 

[jsauber]

Mark858, I figured the input box out. Thanks again for the help.