Results 1 to 7 of 7

Thread: Get Range Address When Length Of Address Greater than 251 / 255

  1. #1
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14

    Get Range Address When Length Of Address Greater than 251 / 255

    I am trying to capture a

    Selection.Address property

    That is longer than 255 characters - So the user selects rows using the mouse (shift+click & Ctrl+click).
    When the user is done selecting the rows - I have a userform with actions - i.e. exporting the selected rows.

    BUT - the Selection.address stops changing at around 251 characters (I added a label that displays the value and the length) - any suggestions how I can trap the selections into and array or something - so the user can make a selection that is any length.

    My main objective is to allow the user to select any number of rows - then select the type action he wants to do with the selection - i.e. Export of data or charting. So if anybody has other suggestions - it is much appreciated.



    Code:
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        If Len(Selection.Address) > 251 Then
            MsgBox "How to deal with this"
        End If
    end usb

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-07-2023 at 01:14 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try...

    Code:
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
         
        Dim objArea As Object
        Dim rngCells As Range
        Dim strAddress As String
        
        If Len(Selection.Address) > 251 Then
            For Each objArea In Target.Areas
                strAddress = strAddress & objArea.Address & ","
            Next objArea
            strAddress = Left(strAddress, Len(strAddress) - 1)
            MsgBox "Len(Selection.Address) = " & Len(strAddress) & vbCrLf & vbCrLf _
                & "The address is " & vbCrLf & vbCrLf & _
                strAddress
       End If
       
    End Sub

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Rasm,

    It seems that you have posted same question at here.

    Please provide a link when cross posting.

    Kris

  4. #4
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    I will do - sorry

  5. #5
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    Here is how I ended up dealing with issue - I keep track of the selected rows in a Listbox on a userform. From that userform I can then have access to an unlimited number of rows in my selection - than do the proper processing of my selected rows

    Code:
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Dim MyString() As String, MyString1() As String
        If Len(Selection.Address(False, False)) > 240 Then
            Astr = Selection.Address
            MyString = Split(Astr, ",")
            For i = 0 To UBound(MyString) - 1
                MyString1 = Split(MyString(i), ":")
                If MyString1(0) <> MyString1(1) Then
                        FormSlt.LBrowsSlt.AddItem MyString1(0) & "-" & MyString1(1)
                    Else
                        FormSlt.LBrowsSlt.AddItem MyString1(0)
                End If
                ActiveSheet.Range(MyString(UBound(MyString))).Select
            Next i
        End If
    End Sub

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Thanks for posting back. I think you can shorten the code little bit, untested though.

    Code:
    Dim MyString() As String, MyString1() As String
        Dim lstList
        
        If Len(Selection.Address(False, False)) > 240 Then
            astr = Replace(Selection.Address, ":", "-")
            lstList = Split(astr, ",")
            FormSlt.LBrowsSlt.List = lstList
        End If

  7. #7
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    I often select using Shift+Click & Ctrl+Click - so I have multiple ranges - some continous - some single rows - some just even rows - so I may have more than a single sellection that is >240 characters - doint it using the loop allow me to have as many loops of selections of >240 as I desire. Since I may have multiple loops where the selection.address is greater than 240 - I dont add the last selection to the additem - instead I use it to for the Select fucntion - that way it become the new entry into the next selection. To drive me insane the FormSlt userform is only a placeholder for the rows selected - so I use it to populate other userforms - that way I can carry the selections across userform (and only having one ListBox to manage - I now use the way you set the list in your code rather than using the additem) - using the Form_Activate rather than the Form_initialize sub allow me to have great userform interaction.

Similar Threads

  1. Replace Pivot Table Source Range with New Address VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 04-25-2013, 07:51 PM
  2. Indirect Address Column Formula help needed
    By trankim in forum Excel Help
    Replies: 4
    Last Post: 10-13-2012, 02:15 PM
  3. Replies: 10
    Last Post: 04-07-2012, 05:33 AM
  4. Get last Filled Cell address in a Range.
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 3
    Last Post: 03-24-2012, 01:08 AM
  5. Using Selection.Address
    By Rasm in forum Excel Help
    Replies: 1
    Last Post: 11-28-2011, 05:20 AM

Posting Permissions

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