VBA to merge range of cells

[eoinymc]

Hi there,

If I have a list of cells, is there any way that Excel VBA can make this into a range?

i.e. if I have "B6, B7, B8, B10, C6, C7, C8, C10" it would shrink it down to "B6:C8, B10:C10"

Anyone got any ideas?

Cheers,

Eoin

 

[puru.sve]

Hi .. you may go for this

Sub Macro()
    Dim rng As Range
    Dim rngText As String
    Dim i As Integer
    Dim arrtext() As String
    
    rngText = "B6,B7,B8,B10,C6,C7,C8,C10"
    arrtext = Split(rngText, ",")
    Set rng = Sheet1.Range(arrtext(LBound(arrtext)))
    For i = LBound(arrtext) + 1 To UBound(arrtext)
        Set rng = Union(rng, Range(arrtext(i)))
    Next i
    
    MsgBox rng.Address
    
End Sub

 

[eoinymc]

Thank you...you have just saved me so much hassle!

I really appreciate your help!