VBA – Select Multiple Cells in a Excel Range

Selecting multiple cells in Excel through VBA may be quite a difficult task. Although, using Select is not always a good practice, sometimes it should be done.

To generate some numeric values, let’s run the following:

Public Sub FullMe()
    
    Dim myCell As Range
    Dim myRange As Range
    Dim cnt As Long
    
    Set myRange = Range("A1:F20")
    
    For Each myCell In myRange
        myCell = cnt
        If cnt = 6 Then cnt = 1
        cnt = cnt + 1
    Next myCell
    
End Sub

After this, we have the excel file from above filled with some “random” numbers. Now the “Select Multiple” trick:

Public Sub TestMe()

    Dim inputRange As Range
    Set inputRange = Range("A1:F20")

    Dim largestNumber As Double
    largestNumber = WorksheetFunction.Max(inputRange)

    Dim myUnion As Range
    Dim myCell As Range

    For Each myCell In inputRange
        If myCell = largestNumber Then
            If Not myUnion Is Nothing Then
                Set myUnion = Union(myUnion, myCell)
            Else
                Set myUnion = myCell
            End If
        End If
    Next myCell

    myUnion.Select

End Sub

myUnion() is used to build up the range of the cells. Thus, it is quite ok that once it is selected, all the cells with the highest value are selected.

Cheers! 🙂