How to know the real range of a dynamic range

[MarcDS]

I use statements as

Worksheets("Cleaned").Names.Add Name:="Date_and_Time", RefersToR1C1:="=Cleaned!R2C1:INDEX(Cleaned!C1, COUNTA(Cleaned!C1))"

Worksheets("Cleaned").Names.Add Name:="Date", RefersToR1C1:="=Cleaned!R2C2:INDEX(Cleaned!C2, COUNTA(Cleaned!C2))"

Worksheets("Cleaned").Names.Add Name:="Month", RefersToR1C1:="=Cleaned!R2C3:INDEX(Cleaned!C3, COUNTA(Cleaned!C3))"

in VBA to define dynamic ranges.



Since I find strange (for me unexplicable) things during the execution of my VBA code I would like to see what is actual range of these ranges.
Example Date_and_Time is A1:a1021, Date is B2:b1021 etc.



Is there a way to do that?

 

[Joe4]

Welcome to the Board!

Something like this would work:

    MsgBox Range("Date_and_Time").Address

By the way, I would recommend NOT using names of built-in Excel functions like "Date" and "Time" as the name of your named ranges.

 

[MarcDS]

Welcome to the Board!

Something like this would work:

    MsgBox Range("Date_and_Time").Address

By the way, I would recommend NOT using names of built-in Excel functions like "Date" and "Time" as the name of your named ranges.

Thanks a lot. I used Worksheets("Cleaned").Range("Date").Address etc as watches. This allowed me to understand why my code behaved so erratically.