How to deal with range strings longer than 25? characters

[Rasm]

I am looking for a good link to deal with range strings that are longer than 255 characters - I think limit is 254 or 255 - not sure.

when Len(Selection.Address) become greater than the magic 25? - then it just quits.

 

[Scott Huish]

Why are you using Selection.Address?

How about

Len(Activecell) ?

 

[Rasm]

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.

 

[Rasm]

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.

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

 

[mikerickson]

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.

 

[Rasm]

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.

 

[Rasm]

Mike - The False,False worked great - thanks - what does the Range.Areas do?

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.

 

[mikerickson]

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

Dim oneArea as Range

For each oneArea in Range("A1:B4, A5:C8, D6:E5")
    MsgBox oneArea.Address
Next oneArea

 

[Rasm]

Thanks - OK - I will try that - but what happens when it become longer than 255 characters? - that is the Range string

 

[Rasm]

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

Dim oneArea as Range
 
For each oneArea in Range("A1:B4, A5:C8, D6:E5")
    MsgBox oneArea.Address
Next oneArea

Mike - How can I use this to read what rows I have selected using the Mouse?