Search code examples
excelvbacell

how to get name of cell in excel with vba

i have searched a lot but i couldnt find. Is there a simple way to get name of cell from range object like "A30" not like "$A$30" i'm asking for without '$' sign

Thanks in advance

Solution

  • If you are talking about removing Absolute Reference from a Range address you have: The function to use is:

    Range("UsedRange").Address([RowAbsolute],[ColumnAbsolute])

    As example, let's suppose your Fully Absolute cell is «$A$1:$F$5000»:

    1. If you want to keep Absolute Row then:

      Range("$A$1:$F$5000").Address(true,false) '<---- Result: A$1:F$5000

    2. If you want to keep Absolute Column then:

      Range("$A$1:$F$5000").Address(false,true) '<--- Result: $A1:$F5000

    3. If you want fully dynamic address (no absolute reference) then:

      Range("$A$1:$F$5000").Address(false,false) '<--- Result: A1:F5000

    Hope it will help you!