Trying to filldown formula on multiple columns to last column and last row

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
82
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi,

Im trying to fill down formulas accross multiple rows, the end of the column may change, and I have that part working, I can select the first row, but then I get stuck filling down the formula to the last row of data.

hers a working code of filling across columns and selecting the row


Dim GRrepLastColumn, lastrowReport As Long
GRrepLastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range(ActiveCell, Cells(ActiveCell.row, GRrepLastColumn)).FillRight


Range("C3").Activate
ActiveCell.FormulaR1C1 = _
"=IF('GR Semi-Final'!RC[-1]=0,"""",'GR Semi-Final'!R2C[-1])"
GRrepLastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range(ActiveCell, Cells(ActiveCell.row, GRrepLastColumn)).FillRight
Range(ActiveCell, Cells(ActiveCell.row, GRrepLastColumn)).Select

after that fill down, autofill, causes and error, so Im stuck on how to get all my formulas to the bottom row.

Please could you help me complete this final bit

thanks for your help
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
798
Office Version
  1. 365
Platform
  1. Windows
I don't fully understand your requirements but just in terms of filling down this should work.
As near as I can figure it GR Semi-Final needs to be the active sheet and assuming that is the case there is no need to reference the sheet name in the formula.
I have commented out the formula with the sheet name and if I am correct you can just delete those 2 lines.
If not uncomment those 2 lines and delete the other 2.


VBA Code:
Sub FillRightandDown()

    Dim GRrepLastColumn As Long, GRrepLastRow As Long
    GRrepLastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    GRrepLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Range("C3").Activate
'    ActiveCell.FormulaR1C1 = _
'    "=IF('GR Semi-Final'!RC[-1]=0,"""",'GR Semi-Final'!R2C[-1])"
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-1]=0,"""",R2C[-1])"
    Range(ActiveCell, Cells(ActiveCell.Row, GRrepLastColumn)).FillRight
    Range(ActiveCell, Cells(GRrepLastRow, GRrepLastColumn)).FillDown


End Sub
 
Solution

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
82
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I don't fully understand your requirements but just in terms of filling down this should work.
As near as I can figure it GR Semi-Final needs to be the active sheet and assuming that is the case there is no need to reference the sheet name in the formula.
I have commented out the formula with the sheet name and if I am correct you can just delete those 2 lines.
If not uncomment those 2 lines and delete the other 2.


VBA Code:
Sub FillRightandDown()

    Dim GRrepLastColumn As Long, GRrepLastRow As Long
    GRrepLastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    GRrepLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    Range("C3").Activate
'    ActiveCell.FormulaR1C1 = _
'    "=IF('GR Semi-Final'!RC[-1]=0,"""",'GR Semi-Final'!R2C[-1])"
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-1]=0,"""",R2C[-1])"
    Range(ActiveCell, Cells(ActiveCell.Row, GRrepLastColumn)).FillRight
    Range(ActiveCell, Cells(GRrepLastRow, GRrepLastColumn)).FillDown


End Sub
Thank you its perfect, works great, I dont suppose you know if you can work a formula to start at a cell reference and end on the last data row, then filter down to the last row.

Range("B3").Activate
ActiveCell.Formula = "=xCONCAT(C3:CZ3)"

trying to make it work with this, (the CZ3 is way over the data in their )but having errors.

Range(ActiveCell, Cells(GRrepLastRow, GRrepLastColumn)).FillDown

Thanks

Dave.
 

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
82
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
The whole thing just picks up what line a PO error line on, from all downloads from SAP it uses multiple worksheets, this is the last one I need to make work.
 

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
82
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Range("B3").Activate
ActiveCell.Formula = "=xCONCAT(C3:CZ3)"
' LastRowReport = Range("A" & Rows.Count).End(xlUp).row
Range("B3").AutoFill Destination:=Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).row)


Keep getting errors on Fill down - Autofill method of Range class failed
'
 

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
82
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Ok got the fill down issue, still not sure if you can have an open formula ref though, C3: end of row.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,706
Messages
5,654,837
Members
418,156
Latest member
juliapearson

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