Selecting Individual Ranges Simulataneously - Cntl Select

Question

In a workbook I can select a range of cells and then hold down the Cntl key to select another range simultaneously and so on. After which I can perform some formatting function like merging those ranges individually, etc.

How can this be done in VBA using Cells()? Like

Range(Range(Cells(x,y),Cells(x,y+3)),Range(Cells(x+1,y),Cells(x+1,y+3))).Select

But this just selects the whole Range(Cells(x,y),Cells(x+1,y+3)) as if it were one big single range.

I've tried what I've shown above and the Union function.

enter image description here

Answer 1

• Use Union method get the reference of multiple ranges

Microsoft documentation:

Application.Union method (Excel)

    Dim r1 As Range, r2 As Range
    Dim rAll As Range
    Set r1 = Range(Cells(x, y), Cells(x, y + 3))
    Set r2 = Range(Cells(x + 1, y), Cells(x + 1, y + 3))
    Set rAll = Application.Union(r1, r2)
    rAll.Select

• Actually, you don't need to select the range.

How to avoid using Select in Excel VBA

• If you try to merge cells

    Dim rArea As Range
    Set rAll = Application.Union(r1, r2)
    ' You may use Union multiple times
'    Set rAll = Application.Union(rAll, r3)
    For Each rArea In rAll.Rows
        rArea.Merge
    Next

Question: cleaner code as opposed to selected each range individually and repeating the format commands over and over

• Refactor the common part as a seperated Sub procedure

Sub demo()
    Range("B2") = "Hello"
    Range("B4") = "Goodbye"
    SetFormat Cells(2, 2).Resize(, 3)
    SetFormat Cells(4, 2).Resize(, 5)
End Sub
Sub SetFormat(rng As Range)
    With rng
        .Font.Color = vbBlue
        .Merge
        ' other formatting code
    End With
End Sub