Selecting multiple cells at a same time having same cell value

Question

in a worksheet I want to select the inputbox's input i.e. suppose "A" which is incurred in multiple cells in multiple location. I want to select all the cells referring to letter "A" at the same time.

Option Explicit

Sub SelectBattleship()
    Dim BattleShip As Range
    Dim Name As String
    Dim store As Variant
    Dim cell As Range

    Set BattleShip = Range("A1:J10")

    Name = InputBox("Value?")

    For Each cell In BattleShip
        If cell = Name Then
            store = cell.AddressLocal & cell.AddressLocal

        End If


    Next cell
    store.Select

End Sub

I expect all the cells containing the letter "A" will be selected together.

Selecting in itself is rarely of use - what do you actually want to do with these cells? https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Aug 06 '19 at 10:35

score 0 · Accepted Answer · answered Aug 06 '19 at 11:11

Build a Union() and use it:

Option Explicit

Sub SelectBattleship()
    Dim BattleShip As Range
    Dim Name As String
    Dim store As Variant
    Dim cell As Range, rSelect As Range

    Set BattleShip = Range("A1:J10")

    Name = InputBox("Value?")

    For Each cell In BattleShip
        If cell.Value = Name Then
            If rSelect Is Nothing Then
                Set rSelect = cell
            Else
                Set rSelect = Union(rSelect, cell)
            End If
        End If
    Next cell
    If rSelect Is Nothing Then

    Else
        rSelect.Select
    End If

End Sub

Selecting multiple cells at a same time having same cell value

1 Answers1