-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How to deal with range strings longer than 25? characters
- Thread starter Rasm
- Start date
Scott Huish
MrExcel MVP
- Joined
- Mar 17, 2004
- Messages
- 20,615
- Office Version
- 365
- Platform
- Windows
Why are you using Selection.Address?
How about
Len(Activecell) ?
How about
Len(Activecell) ?
Upvote
0
I am selecting entire rows - and I read the rows selected using the selection.address - I use Ctrl+click & Shift+Click to select the rows - I have a label displaying Selection.Address & Len(selection.Address) - when it hits 251 characters long - then it quits. So I want to be able to save the selection.address in an array (or any other solution you can suggest) - then clear selection - build it up to max lenghts again - make 2nd array and so on. When the user is done with the row selection I have a userform that then allow the processing of the selected rows - i.e. export . Hope this makes sense.
Upvote
0
OK - I did not get any real respsonses - so I did it myself - But I would appreciate any comments - so I have a userform with a listbox 'LBrowsSlt' - when the the Len(Selection.Address )>240 - I write the selection to my listbox (except last selection)- then I reset the Selection to my last selection - this works - but I am convinced it could be done better.
Comments appreciated.
Comments appreciated.
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim MyString() As String, MyString1() As String
If Len(Selection.Address) > 240 Then
Astr = Selection.Address
MyString = Split(Astr, ",")
For i = 0 To UBound(MyString) - 1
Astr = Replace(MyString(i), "$", "")
MyString1 = Split(Astr, ":")
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
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
In words, what is that code supposed to be doing?
It looks like using the .Areas property of a range object would be a better way to go than splitting the Address at commas.
Also, you can avoid the problem with $ by using .Address(False,False) rathe than .Address.
Is FormSlt.LBrowsSlt a collection? If so, it might be better to use the (inherent) range.Areas collection rather than parsing the sub-address strings.
It looks like using the .Areas property of a range object would be a better way to go than splitting the Address at commas.
Also, you can avoid the problem with $ by using .Address(False,False) rathe than .Address.
Is FormSlt.LBrowsSlt a collection? If so, it might be better to use the (inherent) range.Areas collection rather than parsing the sub-address strings.
Upvote
0
Mike
I am selecting rows using my mouse - However when I select to many rows ( Len(Selection.Address)>251 or so) then the Selection.Address no longer capture the rows I have selected - say you select only every 2nd row using Ctrl+mouse click.
So I look to see when Selection.Address is a certain length (if > 240 then) - then I place all the selections (except for last one) in LBrowsSlt (is a listbox on a userform ) - At the end I reset Selection.address by setting it to the last selected item. Now I can make a bunch more selections (if >240 then add to ListBox) This allow me to have a listbox with an unlimited number of rows - My userform now allow me to process all the selected rows. For instance export those records (rows of selected data) - chart selected rows - do math pretreatment of the data - etc.
I hope this makes sense - lol - I am trying to deal with a Range string that is greater than 255 characters - I think that is the limit of Excel.
Your assistance is much appreciated.
PS -- I do have a separate function that capture the very last Selection.Address - before the user mnakes his slection to export and/or chart the data and/or prepross the data.
I am selecting rows using my mouse - However when I select to many rows ( Len(Selection.Address)>251 or so) then the Selection.Address no longer capture the rows I have selected - say you select only every 2nd row using Ctrl+mouse click.
So I look to see when Selection.Address is a certain length (if > 240 then) - then I place all the selections (except for last one) in LBrowsSlt (is a listbox on a userform ) - At the end I reset Selection.address by setting it to the last selected item. Now I can make a bunch more selections (if >240 then add to ListBox) This allow me to have a listbox with an unlimited number of rows - My userform now allow me to process all the selected rows. For instance export those records (rows of selected data) - chart selected rows - do math pretreatment of the data - etc.
I hope this makes sense - lol - I am trying to deal with a Range string that is greater than 255 characters - I think that is the limit of Excel.
Your assistance is much appreciated.
PS -- I do have a separate function that capture the very last Selection.Address - before the user mnakes his slection to export and/or chart the data and/or prepross the data.
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
Ranges can be discontinous like the range a1:c2, d4:e5, a8:c12
An area is a rectangular region. Every range is composed of areas. Try this to see how it works
An area is a rectangular region. Every range is composed of areas. Try this to see how it works
Code:
Dim oneArea as Range
For each oneArea in Range("A1:B4, A5:C8, D6:E5")
MsgBox oneArea.Address
Next oneArea
Upvote
0
Mike - How can I use this to read what rows I have selected using the Mouse?
Upvote
0
Similar threads
- Question
