CODEWITHSUNDEEP
excelvba
user3340121
user3340121

Reputation: 3

vba for selecting multiple different active cells with offset condition

I am new to vba and I have vba code for activecell with offset codition. The following code selects few cells based on offset condtion. I want to get selected same number of cells if more than one cell or multiple cells are selected. please help me. Thanks in advance

ActiveCell.Offset(, 0).Resize(1, 2).Select

If i select cell b1, then on running the code it selects b1 and c1. I want solution for multiple random cells if selected in column b, the corresponding value should be selected.

Upvotes: 0

Views: 5247

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

If select a1 and run the code it will select a1 and b1. If I select a1 and a3 and a7 then cells a1b1 and a3b3 and a7b7 should be selected.

UPDATED:

Follow up from comments:

Sub test1()
    Dim rng As Range
    Dim ar As Range
    Dim rngAE As Range, cell As Range

    With Selection
        For Each ar In .Areas
            If rng Is Nothing Then
                Set rng = ar.Resize(1, 2)
            Else
                Set rng = Union(rng, ar.Resize(1, 2))
            End If
        Next ar

        Set rngAE = Intersect(.Cells, Range("A:E"))
        If Not rngAE Is Nothing Then
            For Each cell In rngAE
                Set rng = Union(rng, Range("A" & cell.Row & ":E" & cell.Row))
            Next cell                
        End If
    End With

    rng.Select
End Sub

Upvotes: 0

Related Questions