CODEWITHSUNDEEP
vbaexcel
barciewicz
barciewicz

Reputation: 3783

Select non-continuous cells based on criteria

I have a spreadsheet with letter "D" and nothing else put in random cells. What code do I use to select/copy - or even better dim as range - all of those cells?

So far I Have the following:

Sub SelectD()

Dim AllD As Range

For Each cell In ActiveSheet.UsedRange.Cells
    If cell = "D" Then
        Set AllD = '???
    End If
Next cell

End Sub

Thanks, Bartek

Upvotes: 0

Views: 79

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use Union to add the cells to the range as they are found.

Sub SelectD()

Dim AllD As Range

For Each cell In ActiveSheet.UsedRange.Cells
    If cell = "D" Then
        If AllD Is Nothing then
            Set AllD = cell
        Else
            Set AllD = Union(cell,AllD)
        End If
    End If
Next cell

'Do something with AllD
Debug.Print AllD.Address
End Sub

Upvotes: 1

Related Questions