Add to irregular cell selection with VBA-CodePudding

I'm trying to add irregular cells into a selection via VBA.

In the code below, there are some cells marked with 1's in column E on a sheet. It's supposed to loop through that column and make a a selection string, then use that string to select the corresponding cells in column A, as a single selection block.

My problem is that the output string will only work if I dump it to a spare cell, then manually copy it into VBA.

Any thoughts appreciated!

Sub AddToSelection()

Dim B As Worksheet, SelectString As String, RX As String
Set B = ActiveSheet

For C = 1 To 20
    If B.Cells(C, 5) = 1 Then
    
        If RX = "" Then
            RX = """" & "A" & B.Cells(C, 5).Row
            Else
            RX = RX & "," & "A" & B.Cells(C, 5).Row
            End If
        End If
    Next C

RX = RX & """"

Range(RX).Select 'does NOT work



''''BUT

B.Cells(1, 10) = RX ' I dump RX into a cell

SelectString = "A3,A5,A8" ' this copied manually from cell above, so is exactly RX

Range(SelectString).Select 'now it works


End Sub

CodePudding user response：

Your quotes are off (shouldn't be adding """" to the beginning and end), but it's much better to use Union here:

For C = 1 To 20
    If B.Cells(C, 5) = 1 Then
        Dim rng As Range
        If rng Is Nothing Then
            Set rng = B.Cells(C, "A")  ' or B.Range("A" & C)
        Else
            Set rng = Union(rng, B.Cells(C, "A"))
        End If
    End If
Next C

If Not rng Is Nothing Then
    rng.Select
End If