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

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

---

Hi Rasm,

It seems that you have posted same question at here.

Please provide a link when cross posting.

Kris

I will do - sorry

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

---

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

---

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.