Fill down formula to last row-dynamic

gazmoz17

New Member
Joined
Sep 18, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hi,

Seen many threads on this with specific examples relating to posters data sheet e.g. specific range and required formula.

I want a macro button that fills down to last row regardless of what spreadsheet I'm in? So enter formula in for example (filtered list) A2 wish to then press a button which copies it down from A3 onwards. Either by dropping into A3 and triggering hotkey or after keyed formula in A2. However, dont want the code to ref A2 or A3 but be dynamic based on selection etc.

Many Thanks
Gareth
 

Excel Facts

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

LloydFinancials

Well-known Member
Joined
Apr 24, 2015
Messages
521
I created a macro/process that utilizes a hotkey.

Save this macro in an .xlsm workbook. Go to the Developer tab on the ribbon, select 'Macros.' Click on options, enter a capital K in the shortcut key. Hit OK and x out of dialogue box. Keep it open. Go to the workbook that has the formula you want copied down. Select the cell and hit shift + control + K.

Sub Macro2()
'
' Keyboard Shortcut: Ctrl+Shift+K
'
Dim a As Long
a = ActiveCell.SpecialCells(xlLastCell).Row
Selection.AutoFill Destination:=Range(Cells(ActiveCell.Row, ActiveCell.Column).Address & ":" & _
Cells(a, ActiveCell.Column).Address)
End Sub

Note: Last row is determined by the last formattable cell in the worksheet. Both workbooks need to be in the same Excel instance. Modify as needed.
 
Solution

Forum statistics

Threads
1,144,629
Messages
5,725,390
Members
422,623
Latest member
Dave52

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