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.

 

[mikerickson]

Try something like this.

In a normal module

Public selectedRange As Range

Sub ShowSelectedAddress()
    If selectedRange Is Nothing Then
        MsgBox "nothing selected"
    Else
        MsgBox selectedRange.Address & " has been accumulated."
    End If
    Set selectedRange = Nothing:Rem reset selectedRange 
End Sub

Sub showUF()
    UserForm1.Show
End Sub

In the sheet's code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If selectedRange Is Nothing Then
        Set selectedRange = Target
    Else
        Set selectedRange = Application.Intersect(UsedRange, Application.Union(selectedRange, Target))
    End If
End Sub

and in the userform's module

Private Sub UserForm_Initialize()
    Dim oneCell As Range
    If Not selectedRange Is Nothing Then
        Me.Caption = selectedRange.Cells.Count
        For Each oneCell In selectedRange
            ListBox1.AddItem CStr(oneCell)
        Next oneCell
    End If
End Sub

The basic change I'm suggesting is to combine the Selections into a Range object, not a collection of strings.

 

[Rasm]

Mike
I will try that - thanks a bunch

 

[Krishnakumar]

Cross Post