Selection.AutoFill help

MikeL

Active Member
Joined
Mar 17, 2002
Messages
440
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
Mar 17, 2002
Messages
440
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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):
Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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):
Code:
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

Active Member
Joined
Mar 17, 2002
Messages
440
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top