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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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! 🙂