Autofill specific range which is dynamic

[sourabh_ajmera]

Hello Everybody,

I need your help in correcting this code. I need a range from specific row to specific column.
The row and column has to be dynamic as it changes everyday. I have been through few posts on this but nothing what fullfilled my requirement.

Code:

'Last Column
Dim col As Integer
col = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
MsgBox col

'Last Row
Dim row As Long
row = Cells.Find("*", [A1], , , xlByRows, xlPrevious).row
MsgBox row

ActiveWorkbook.Names("td").RefersToR1C1 = "=TD!R1C1:R" & row & "C" & col & ""
ActiveWorkbook.Names("titletd").RefersToR1C1 = "=TD!R1C11:R1C" & (col - 14) & ""

Range("FU2").Select
ActiveCell.Formula = _
"=IFERROR(AVERAGEIFS(RC11:RC165,titletd,"">=""&MONTH(R1C)&""/1/""&YEAR(R1C),titletd,""<=""&MONTH(R1C)&""/""&DAY(DATE(YEAR(R1C),MONTH(R1C)+1,0))&""/""&YEAR(R1C)),0)"


Selection.AutoFill Destination:=Range(Cells(2, (col - 6)), Cells(2, col)).Select, Type:=xlFillDefault <--- This is where I need help to make it dynamic

'Selection.AutoFill Destination:=Range("FU2:GA2"), Type:=xlFillDefault <--- This is the range

Any suggestions? Please help even if its a small mistake.
Thank you in advance.

 

[pbornemeier]

This works, I am not sure why it is needed.

    col = 183 'GA   1877=FU
    Range("FU2").AutoFill Destination:=Range("FU2:GA2"), Type:=xlFillDefault
    Range("FU2").AutoFill Destination:=Range(Range(Cells(2, (col - 6)), Cells(2, col)).Address), Type:=xlFillDefault

 

[sourabh_ajmera]

Thanks @pbornemeier....it works!!

I also came up with another solution

Found out the column header name and converted that into column no and than to column letter
same with the last column. Found out the last column and converted it into column letter and then used it as below:

Code:
Selection.AutoFill Destination:=Range(JanCol3 & "2:" & coltxt3 & "2"), Type:=xlFillDefault