VBA／select contiguous cells as separate batches

[smallxyz]

Hi

Say, A1:A6 are to be selected but with 2 cells as a group, i.e. A1:A2,A3:A4,A5:A6
I tried

With ActiveSheet
Union( .Range("A1:A2"), .Range("A3:A4"), Range("A5:A6")).Select
End With

However, the result is A1:A6 being selected instead of 3 selection batches A1:A2, A3:A4 and A5:A6
The above is a simplified case of my scenario. I just wonder, if in general, there is a way for VBA to group selected contiguous cells by N number as a batch, and return contiguous selection as separate batches ( like what we select using Ctrl + MouseLeftClick on cells ).

 

[Joe4]

You don't want to use UNION, as that combines them all into one range.
Try this:

With ActiveSheet
    .Range("A1:A2,A3:A4,A5:A6").Select
End With

 

[smallxyz]

You don't want to use UNION, as that combines them all into one range.
Try this:

With ActiveSheet
    .Range("A1:A2,A3:A4,A5:A6").Select
End With

Thanks.

That works for a certain size of batched cells.
I just tried extending to "A1:A2,A3:A4, ... , A99:A100". It declines the selection.
Is it possible to bypass this limit ?

 

[Joe4]

What exactly are you trying to accomplish?
What is the end goal her?

 

[smallxyz]

What exactly are you trying to accomplish?
What is the end goal her?

I have another VBA code to perform other tasks upon those selected batch.

But the prerequisite is to first successfully select those contiguous cells in batch level.

Number one entry barrier.

 

[JEC]

This works: .Range("A1:A2,A3:A4,A5:A6,A99:A100").Select

But it is only selecting A99 and A100 (and the rest). Working as it should

 

[smallxyz]

This works: .Range("A1:A2,A3:A4,A5:A6,A99:A100").Select

But it is only selecting A99 and A100 (and the rest). Working as it should

Try

Range("A1:A2,A3:A4,A5:A6,A7:A8,A9:A10,A11:A12,A13:A14,A15:A16,A17:A18,A19:A20,A21:A22,A23:A24,A25:A26,A27:A28,A29:A30,A31:A32,A33:A34,A35:A36,A37:A38,A39:A40,A41:A42,A43:A44,A45:A46,A47:A48,A49:A50,A51:A52,A53:A54,A55:A56,A57:A58,A59:A60,A61:A62,A63:A64,A65:A66,A67:A68,A69:A70,A71:A72,A73:A74,A75:A76,A77:A78,A79:A80,A81:A82,A83:A84,A85:A86,A87:A88,A89:A90,A91:A92,A93:A94,A95:A96,A97:A98,A99:A100").Select

 

[JEC]

Ah that is the 255 char limit in the range object. Try

Sub jec()
 Dim ar As Range, it, areaString
 areaString = "A1:A2,A3:A4,A5:A6,A7:A8,A9:A10,A11:A12,A13:A14,A15:A16,A17:A18,A19:A20,A21:A22,A23:A24,A25:A26,A27:A28,A29:A30,A31:A32,A33:A34,A35:A36,A37:A38,A39:A40,A41:A42,A43:A44,A45:A46,A47:A48,A49:A50,A51:A52,A53:A54,A55:A56,A57:A58,A59:A60,A61:A62,A63:A64,A65:A66,A67:A68,A69:A70,A71:A72,A73:A74,A75:A76,A77:A78,A79:A80,A81:A82,A83:A84,A85:A86,A87:A88,A89:A90,A91:A92,A93:A94,A95:A96,A97:A98,A99:A100"
 For Each it In Split(areaString, ",")
  If Not ar Is Nothing Then Set ar = Union(ar, Range(it)) Else Set ar = Range(it)
 Next
 ar.Select
End Sub

 

[smallxyz]

Ah that is the 255 char limit in the range object. Try

Sub jec()
 Dim ar As Range, it, areaString
 areaString = "A1:A2,A3:A4,A5:A6,A7:A8,A9:A10,A11:A12,A13:A14,A15:A16,A17:A18,A19:A20,A21:A22,A23:A24,A25:A26,A27:A28,A29:A30,A31:A32,A33:A34,A35:A36,A37:A38,A39:A40,A41:A42,A43:A44,A45:A46,A47:A48,A49:A50,A51:A52,A53:A54,A55:A56,A57:A58,A59:A60,A61:A62,A63:A64,A65:A66,A67:A68,A69:A70,A71:A72,A73:A74,A75:A76,A77:A78,A79:A80,A81:A82,A83:A84,A85:A86,A87:A88,A89:A90,A91:A92,A93:A94,A95:A96,A97:A98,A99:A100"
 For Each it In Split(areaString, ",")
  If Not ar Is Nothing Then Set ar = Union(ar, Range(it)) Else Set ar = Range(it)
 Next
 ar.Select
End Sub

Good try but nop.
It selects the range A1:A100 as a single batch, instead of batches A1:A2, A3:A4, etc.

I think I will apply the code advised by Joe4 repeatedly several times on the region Range property at best can support.

Thanks all.

 

[Alex Blakenburg]

You didn't really answer @Joe4's questions in post #4, it is not considered to be good practice to "select" objects in VBA so giving that as a goal doesn't really help.

Are you sure one of the 2 options below wouldn't work for you ?

Sub RangeAreas_UsingArray()

    Dim rngFull As Range
    Dim rngPart As Range
    Dim sAddr As Variant
    Dim i As Long
    
    sAddr = Array("A1:A2", "A3:A4", "A5:A6")
    
    For i = 0 To UBound(sAddr)
        Debug.Print Range(sAddr(i)).Address, Range(sAddr(i)).Cells.Count
    Next i

End Sub


Sub RangeAreas_UsingString()

    Dim rngFull As Range
    Dim rngPart As Range
    Dim sAddr As Variant
    Dim i As Long
    
    sAddr = Split("A1:A2,A3:A4,A5:A6", ",")
    
    For i = 0 To UBound(sAddr)
        Debug.Print Range(sAddr(i)).Address, Range(sAddr(i)).Cells.Count
    Next i

End Sub