Convert a combination of ranges into only one range

[radames.rios]

I have a range in VBA that says for example:

(A1, A2, A3, A4, A6, A7, A8, A13, A15)

Is it possible to merge that range together and simplify it into:

(A1:A4, A6:A8, A13, A15)

Note that the range covers cells from A1 to A15 but excluding some of the cells in that set.

There is no specific pattern when selecting the cells.

 

[VoG]

For example

Range("A1:A4,A6:A8,A13,A15").Copy

 

[radames.rios]

For example

Range("A1:A4,A6:A8,A13,A15").Copy

What I meant was that the result I wanted was the simplified version:

"A1:A4,A6:A8,A13,A15"

 

[VoG]

I don't know what you mean by "the simplified version".

 

[Rick Rothstein]

What I meant was that the result I wanted was the simplified version:

"A1:A4,A6:A8,A13,A15"

If you have those addresses as a comma delimited text string, then I think this function does what you want...

Function Shorten(ByVal S As String) As String
  Shorten = Intersect(Cells, Range(S)).Address(0, 0)
End Function

Or, if you are selecting the cells, then this function instead...

Function Shorten() As String
  Shorten = Intersect(Cells, Selection).Address(0, 0)
End Function

 

[pgc01]

Hi

... and another way:

Sub Test()
Dim r As Range

Set r = Range("A1, A2, A3, A4, A6, A7, A8, A13, A15")
Set r = Union(r, r)

MsgBox "r: " & r.Address
End Sub

 

[radames.rios]

Hi

... and another way:

Sub Test()
Dim r As Range

Set r = Range("A1, A2, A3, A4, A6, A7, A8, A13, A15")
Set r = Union(r, r)

MsgBox "r: " & r.Address
End Sub

This one worked simply and great! Thanks a lot!

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[Rick Rothstein]

If you have those addresses as a comma delimited text string, then I think this function does what you want...

Function Shorten(ByVal S As String) As String
  Shorten = Intersect(Cells, Range(S)).Address(0, 0)
End Function

Or, if you are selecting the cells, then this function instead...

Function Shorten() As String
  Shorten = Intersect(Cells, Selection).Address(0, 0)
End Function

I am not sure if you missed my response (shown above) or if you did not want a function-type solution, so I have recast the code above into macro form which may be the way you wanted to see it...

Sub Test()
  MsgBox Intersect(Cells, Range("A1, A2, A3, A4, A6, A7, A8, A13, A15")).Address(0, 0)
End Sub

If you wanted to see it in a less compacted and perhaps more usable form for you...

Sub Test2()
  Dim Addresses As String, ShortenedAddresses As String
  
  Addresses = "A1, A2, A3, A4, A6, A7, A8, A13, A15"
  ShortenedAddresses = Intersect(Cells, Range(Addresses)).Address(0, 0)
  
  MsgBox ShortenedAddresses
End Sub