Selection.AutoFill help

[MikeL]

Hello,
I have a current line of code (below) that works beginning at cell D5 and autofills to the end of data in col C. I am looking to make this code global to use in other macros.
How would I replace the references specific to D5:D and C with global references?

==================================
Code:
Dim MySelection As Range
Set MySelection = Application.InputBox(prompt:="Select one cell", Type:=8)
MySelection.Select

Selection.AutoFill Destination:=Range("D5:D" & Range("C" & Rows.Count).End(xlUp).row)
==================================

Thanks in advance.

 

[JoeMo]

Your selection is a single cell. The destination must include that cell and the user has to provide input to determine how far the autofill is to extend. Once the user selects the source cell, you can use that input to determine the last filled cell in the source row (last column) or the last filled cell in the source column (last row) if that helps.

 

[MikeL]

Thanks..Looking for code on how to accomplish that..searching and hadn't come across anything. can I use relative reference to select a column to the left of the selected cell.now sure of the xyntax?

 

[JoeMo]

Thanks..Looking for code on how to accomplish that..searching and hadn't come across anything. can I use relative reference to select a column to the left of the selected cell.now sure of the xyntax?

This can be adapted to your need (the user determines the single cell selected and you can then find the last row - lR and the last column lC relative to the selected cell):

Sub test()
Dim C As Long, R As Long, lR As Long, lC As Long
With Selection
   R = .Row
   C = .Column
End With
lR = Cells(Rows.Count, C).End(xlUp).Row
lC = Cells(R, Columns.Count).End(xlToLeft).Column
MsgBox "last Row is " & lR & " last column is " & lC
End Sub
[code]

 

[JoeMo]

Thanks..Looking for code on how to accomplish that..searching and hadn't come across anything. can I use relative reference to select a column to the left of the selected cell.now sure of the xyntax?

This can be adapted to your need (the user determines the single cell selected and you can then find the last row - lR and the last column lC relative to the selected cell):

Sub test()
Dim C As Long, R As Long, lR As Long, lC As Long
With Selection
   R = .Row
   C = .Column
End With
lR = Cells(Rows.Count, C).End(xlUp).Row
lC = Cells(R, Columns.Count).End(xlToLeft).Column
MsgBox "last Row is " & lR & " last column is " & lC
End Sub

 

[MikeL]

Thanks Joe.
I came across a thread that addressed a similar question to mine..
http://www.mrexcel.com/forum/showthread.php?616291-Macro-formula-autofill-from-relative-cell

I honestly don't have a good understanding of the code in the last post on that thread, but it does work in the 2 situations that I tried it...

===================================

Dim MySelection As Range
Dim LastRow As Long

'INPUTBOX SELECT CELL
Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8)
MySelection.Select

'FORMULA
'Do some function

'AUTOFILL TO LAST ROW
LastRow = Columns(1).Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(LastRow, ActiveCell.Column))