Identifying the column of a variable ActiveCell to use in a range

[ChrissyO]

Hi, i am fairly new to VBA and so far this site has helped with every issue i have encountered. However, i have hit a hurdle with my latest efforts and decided to ask the question directly.

I am trying to create a macro that will copy the formula from the ActiveCell (which is variable) down the ActiveCell column to the last row on the worksheet.

I have identified the last row using data in column A (this is always static) but im having issues with combining the ActiveCell column with the identified last row for the purpose of the range.

At the moment i have this, the red is the part of the range i want to reference the ActiveCell column:


LastRow = Range("A65536").End(xlUp).Row

Range (ActiveCell).copy Destination:=Range(ActiveCell:" ???? & LastRow)


Any help would be much appreciated as im sure there is probably an easier way to do this. Like i said this is new to me but extremely interesting.

Thank you

 

[Ruddles]

Try:-

[FONT=Fixedsys]lastrow = Range("A65536").End(xlUp).Row[/FONT]
 
[FONT=Fixedsys]columnletter = Replace(Cells(1, ActiveCell.Column).Address(False, False), "1", "")[/FONT]
 
[FONT=Fixedsys]ActiveCell.AutoFill [/FONT][FONT=Fixedsys]Destination:=Range(ActiveCell.Address(0,0) & ":" & columnletter & lastrow), Type:=xlFillDefault[/FONT]

 

[Sektor]

Sub F()
    Range(ActiveCell, ActiveCell.End(xlDown)).Formula = ActiveCell.Formula
End Sub

 

[RoryA]

Try:

LastRow = Range("A65536").End(xlUp).Row
Range(ActiveCell, cells(LastRow, Activecell.column)).Formula = ActiveCell.Formula

 

[ChrissyO]

Thanks for replies guys, i am amazed at how quick people respond on here.

I tried all of them and they all worked so spoilt for choice really.

Thanks again, it really is very much appreciated.

 

[Ruddles]

Use the one which looks the most instinctively understandable to you. Not mine then!

PS. I'm surprised you only got three working solutions in a whole quarter of an hour though. This forum must be slipping...

 

[RoryA]

They don't all do the same thing, by the way. End(xlDown) will not always produce the same range as your LastRow variable will.