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

 

[rlv01]

A very simple macro like this will do it:

Sub CellSelect()
    If ActiveCell.Column = 1 Then
        ActiveCell.Offset(0, 4).Resize(, 4).Select
    End If
End Sub

But you need to think about how you want to activate the macro.

 

[Will85]

A very simple macro like this will do it:

Sub CellSelect()
    If ActiveCell.Column = 1 Then
        ActiveCell.Offset(0, 4).Resize(, 4).Select
    End If
End Sub

But you need to think about how you want to activate the macro.

My preference, if possible (for other applicable reasons) would be to rebuild the cell reference.

I believe I have to define the row reference from the active cell address and then reference that defined value when building the actual range I want VBA to select?

Something like Activesheet.Range("E"&ROW":"&"H"&ROW).Select

Where ROW is the numeric value of the Activecell, example Active Cell = A1, ROW would be 1. Is there a way to get the row value from the active cell address, define it, and then refernece it to rebuild the reference

 

[rlv01]

Replace this

            ActiveCell.Offset(0, 4).Resize(, 4).Select

with this

            ActiveSheet.Range("E" & ActiveCell.Row & ":H" & ActiveCell.Row).Select

 

[Burrgogi]

Another option:

Sub Select_Cells()
Dim ws As Worksheet
Dim rng As Range
Dim r As Long
Set rng = ActiveCell
Set ws = ActiveSheet
r = ActiveCell.Row
With ws
    If ActiveCell.Column = 1 Then
        Range("E" & r & ":H" & r).Select
    Else: MsgBox "Nothing has been selected in column A!"
    End If
End With
End Sub

 

[Burrgogi]

I'm still learning VBA myself but I'd like to add a couple of comments. Firstly, I gave you my answer above because that was your specific preference. However, I think you would be better off with @rlv01 version. Anytime you can refer to a row or rows using ActiveCell.Row and use that directly, it's faster & more efficient. In a case like yours where the macro executes a simple task like selecting a group of 4 cells it won't matter too much, but I would avoid storing that row number as a variable unless you absolutely must. That's just my personal preference as someone who is still learning the ropes.

The following is another version of my script above that does not rely on a variable to hold the row number.

Sub Select_cells_v2()
Dim ws As Worksheet
Dim rng As Range
Set rng = ActiveCell
Set ws = ActiveSheet
With ws
    If ActiveCell.Column = 1 Then
        ActiveCell.Offset(, 4).Resize(, 4).Select
    Else: MsgBox "Nothing has been selected in column A!"
    End If
End With
End Sub

 

[Burrgogi]

I got some helpful advice from another forum member. Here is a cleaned up version:

Sub Select_cells_v2()
Dim rng As Range
Set rng = ActiveCell
    If rng.Column = 1 Then
        rng.Offset(, 4).Resize(, 4).Select
    Else: MsgBox "Nothing has been selected in column A!"
    End If
End Sub

 

[Will85]

I got some helpful advice from another forum member. Here is a cleaned up version:

Sub Select_cells_v2()
Dim rng As Range
Set rng = ActiveCell
    If rng.Column = 1 Then
        rng.Offset(, 4).Resize(, 4).Select
    Else: MsgBox "Nothing has been selected in column A!"
    End If
End Sub

This works great, what if we didnt want to limit it Column 1, remove the requirement it has to be in column 1

 

[Burrgogi]

This works great, what if we didnt want to limit it Column 1, remove the requirement it has to be in column 1

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

 

[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

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