Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Named Column Range to Variable (97 sp2)

    I have a named range across several columns. My client may insert or remove columns within this range.

    How can I assign the limits of the column range to a variable?

    For example, the original range may be N1:X1, so if my client inserts a column within this range the named range will become N1:T1.

    I want my variable to display N:T. Is there a way to do this?

    Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Named Column Range to Variable (97 sp2)

    How is inserting a column into N1:X1 going to change it to N1:T1? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Column Range to Variable (97 sp2)

    Hans,

    Sorry, my mistake, forgot my alphabet...
    Inserting a column into N1:X1 will, of course, change it to N1:Y1

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Named Column Range to Variable (97 sp2)

    You can use this function:

    Function ColumnRange(strRangeName)
    Dim strAddress As String
    Dim strResult As String
    Dim i As Integer
    Application.Volatile
    strAddress = Range(strRangeName).Address(False, False)
    For i = 1 To Len(strAddress)
    If Not IsNumeric(Mid(strAddress, i, 1)) Then
    strResult = strResult & Mid(strAddress, i, 1)
    End If
    Next i
    ColumnRange = strResult
    End Function

    You can use it in VBA:

    strRange = ColumnRange("MyRange")

    or in a formula in a worksheet:

    =ColumnRange("MyRange")

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Column Range to Variable (97 sp2)

    Thanks, Hans, works great as always.

    Cheers.

Posting Permissions

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