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

[Dave01]

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

 

[Alex Blakenburg]

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.

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

 

[Dave01]

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.

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]

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]

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]

Ok got the fill down issue, still not sure if you can have an open formula ref though, C3: end of row.