How do I select two cells simultaneously through VBA selecting each cell with a different code i.e step by step selection

Question

How do i select cells simultaneously in VBA step by step i.e I do not want to select all the cells at once with one single line of code for e.g. using Range or Union which selects simultaneously. I want to select Cells(1,2) and Cells(5,4) simultaneously but one at a time.

Answer 1

I used a macro on an excel file to see what the vba output would be by selecting two cells simultaneously by selecting one cell then holding the control button then selecting the second cell.

The VBA code uses a range. Which I know you didn't want to use. That being said you can incremently add cells to the range and it will have the look of the cells being selected one at a time as such:

Dim cell1 As String
Dim cell2 As String
Dim cell3 As String

cell1 = "B1"
cell2 = "D5"
cell3 = "B7"

Dim rangeStr As String
rangeStr = cell1
Range(rangeStr).Select ' here it only selects cell B1
rangeStr = cell1 & "," & cell2
Range(rangeStr).Select ' here it incremently also selects cell D5
rangeStr = cell1 & "," & cell2 & "," & cell3
Range(rangeStr).Select ' and here it incremently also selects B7

Answer 2

you may want to use arrays

here's an address approach

Sub Selections()
    Dim rngAddress As String
    Dim myCellsAddresses As Variant, cellAddress As Variant

    myCellsAddresses = Array("B1", "D5", "B7") '<--| collect your cells adresses in an array
    rngAddress = myCellsAddresses(LBound(myCellsAddresses)) '<--| set your first range address as the first cell one
    For Each cellAddress In myCellsAddresses '<--| loop through cells addresses array
        rngAddress = rngAddress & "," & cellAddress '<--| update range address by adding current cell one
        Range(rngAddress).Select '<--| select the resulting range
    Next cellAddress
End Sub

whose Range approach equivalent is:

Sub selections3()
    Dim rng As Range
    Dim myCellsAddresses As Variant, cellAddress As Variant

    myCellsAddresses = Array("B1", "D5", "B7")
    Set rng = Range(myCellsAddresses(0))
    For Each cellAddress In myCellsAddresses
        Set rng = Union(rng, Range(cellAddress))
        rng.Select
    Next cellAddress
End Sub