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

[Rasm]

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.





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 (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

Try...



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

[Admin]

Hi Rasm,

It seems that you have posted same question at here (http://www.mrexcel.com/forum/showthread.php?t=540662).

Please provide a link when cross posting (http://www.excelguru.ca/node/7).

Kris

[Rasm]

I will do - sorry

[Rasm]

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



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))).Sele ct
Next i
End If
End Sub

[Admin]

Hi,

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


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

[Rasm]

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.