Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Identifying column letter (2003)

    Hi. I'm using VBA to loop through every column in a range and using 'Columns(x).Address' to display the address of those columns which are hidden within the range. It currently displays the address as, for example, '$D$1:$D:$23'.
    I would like it to just display 'Column D is hidden'. Which property can I use to display just the column letter? (I could use 'Left(...,2)' to extract the letter but this won't work if the area extends beyond column Z.)
    Thanks, Andy.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Identifying column letter (2003)

    <code>replace(cells(1, x).address(false,false),"1","")</code>
    will give you the column letter.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying column letter (2003)

    Thank you. Strange that there isn't a simple property to extract the column letter. Andy.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Identifying column letter (2003)

    I guess because it's not necessary generally. It's only really useful for a message to the user specifically about a column address, and it's easy enough to calculate. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •