Macro to select last cell in another sheet

[wizardmagu]

I am trying to reference the last cell containing data in the "DT_Data" sheet. I recorded the macro and pressed "Ctrl+↓) to select it, but it is referencing that same cell. I pasted a different date range and now will only go as far as the information had when I recorded the macro. Any help would be greatly appreciated



Range("B2").Select
ActiveCell.FormulaR1C1 = "='DT_ Data'!R[49]C[-1]"

 

[wizardmagu]

anybody?

 

[mumps]

Maybe:

Sub Test()
    Range("B2") = Sheets("DT_ Data").Cells(Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row, Cells.Find("*", , , , xlByColumns, xlPrevious).Column)
End Sub

 

[UWMmakow]

I am trying to reference the last cell containing data in the "DT_Data" sheet. I recorded the macro and pressed "Ctrl+↓) to select it, but it is referencing that same cell. I pasted a different date range and now will only go as far as the information had when I recorded the macro. Any help would be greatly appreciated



Range("B2").Select
ActiveCell.FormulaR1C1 = "='DT_ Data'!R[49]C[-1]"

Range("B2").Value = Sheets("DT_Date").Range("A1").End(xlDown).Row

Replace the bold A with any column that will always have a value in every cell all the way to the last row.

 

[mumps]

The macro I suggested will return the value of the last used cell in the sheet into B2.