VBA Use active.row to build selection

[Will85]

I need help building a simple macro to select a range of cells based on an active cell.

The columns are predefined, but the row is variable.

Example

The active cell is A1, I want a macro that will extrapolate that row number and use it to select cells E1:H1
The active cell is A2, select E2:H2

The active cell will only ever be in column A

 

[Burrgogi]

Not possible to keep the offset and resize language, so as not to hard code the columns?

It certainly is possible to do that but my only concern is that it might produce inconsistent results if something in column A is not selected first. If you are the only user of this macro, then please by all means have at it but I would not trust a perfect outcome each time if other people will be using this macro.

Sub Select_cells_v2()
Dim rng As Range
Set rng = ActiveCell
rng.Offset(, 4).Resize(, 4).Select
End Sub

To clarify further: If other people will be using this macro, you can't always guarantee that they'll have something in column A selected first. Humans being humans, some user errors are bound to occur. This is the reason why I hard coded column E-H in my previous version.

 

[Will85]

You would have to specify the columns you want to select in your code like this:

Sub Select_cells_v2()
Dim r As Long
r = ActiveCell.Row
        Range("E" & r & ":H" & r).Select
End Sub

Now I am trying to select multiple ranges

Range("D" & r & ":H" & r, "J" & r & ":J" & r, "N" & r & ":P" & r).Select


Assuming r is 5
Trying to get it to Select D5:H5, J5, and N5:P5 at the same time, but its not working. Thoughts?

 

[Burrgogi]

Now I am trying to select multiple ranges

Range("D" & r & ":H" & r, "J" & r & ":J" & r, "N" & r & ":P" & r).Select


Assuming r is 5
Trying to get it to Select D5:H5, J5, and N5:P5 at the same time, but its not working. Thoughts?

You can use Union to select non-contiguous cells.

Sub Select_cells_v3()
Dim r As Long
Dim rng As Range
r = ActiveCell.Row
    With ActiveSheet
        Set rng = Union(.Range("D" & r & ":H" & r), .Range("J" & r), .Range("N" & r & ":P" & r))
        rng.Select
    End With
End Sub

 

[Akuini]

Now I am trying to select multiple ranges

Range("D" & r & ":H" & r, "J" & r & ":J" & r, "N" & r & ":P" & r).Select

Try:

Range("D" & r & ":H" & r & ",J" & r & ",N" & r & ":P" & r).Select

 

[Will85]

Try:

Range("D" & r & ":H" & r & ",J" & r & ",N" & r & ":P" & r).Select

Thank you, that works great

 

[Will85]

You can use Union to select non-contiguous cells.

Sub Select_cells_v3()
Dim r As Long
Dim rng As Range
r = ActiveCell.Row
    With ActiveSheet
        Set rng = Union(.Range("D" & r & ":H" & r), .Range("J" & r), .Range("N" & r & ":P" & r))
        rng.Select
    End With
End Sub

Thank you for all your help

 

[Akuini]

You're welcome, glad we could help & thanks for the feedback.