Selecting specific cells around active cells?

Question

I have the code below which I use to select the non-blank cells in column C from row 6 downwards. For each cell that is selected, I now want the code to also select the cells in columns 1 to 10 of that row- but I'm struggling! Any help would be great!

Sub EnquiryPrep()
Dim x As Integer
Dim rng As Range
With Sheets("Sheet 1")
LR = .Range("C" & Rows.Count).End(xlUp).Row
For Each cell In .Range("C6:C" & LR)
    If cell.Value <> "" Then
        If rng Is Nothing Then
            Set rng = (cell)
        Else
            Set rng = Union(rng, cell)
        End If
    End If
    Next cell
rng.Select
End With
End Sub

JosieP JosieP · Accepted Answer · 2013-08-15T10:25:17

perhaps simply

Sub EnquiryPrep()
Dim x As Integer
Dim rng As Range
With Sheets("Sheet 1")
LR = .Range("C" & Rows.Count).End(xlUp).Row
For Each cell In .Range("C6:C" & LR)
    If cell.Value <> "" Then
        If rng Is Nothing Then
            Set rng = cell.offset(, -2).resize(, 10)
        Else
            Set rng = Union(rng, cell.offset(, -2).resize(, 10))
        End If
    End If
    Next cell
rng.Select
End With
End Sub

Selecting specific cells around active cells?

1 Answers