Roll column forward and hard paste previous

[colindickson]

Hi,

I have the following code to copy a formula forward to the next blank column and hard paste the previous one:


Sub RollColumn() Dim UsdCols As Long

UsdCols = Cells(2, Columns.Count).End(xlToLeft).Column
With Range(Cells(2, UsdCols), Cells(76, UsdCols))
.Copy .Offset(, 1)
.Value = .Value




End With

End Sub




Is it possible to change the row 76 in the range to the last used row in that range?

Thanks

[p45cal]

Sub RollColumn()
Dim UsdCols As Long
UsdCols = Cells(2, Columns.Count).End(xlToLeft).Column
lastRow = Cells(Rows.Count, UsdCols).End(xlUp).Row
With Range(Cells(2, UsdCols), Cells(lastRow, UsdCols))
.Copy .Offset(, 1)
.Value = .Value
End With
End Sub