VBA: Multiple SelectionChange Events

[Liz_bbf]

I am extremely new to VBA, so I hope that this is easy to do and I am just missing the obvious. I have code that defines a named range as the active row, and another that does the same for the active column. How to I combine the two into one sub so that I can automatically calculate active row and column at the same time? I am using these named ranges in various formulas.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names("ActiveRow")
.Name = "ActiveRow"
.RefersToR1C1 = "=" & ActiveCell.Row
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names("ActiveColumn")
.Name = "ActiveColumn"
.RefersToR1C1 = "=" & ActiveCell.Column
End With
End Sub

 

[Micron]

I'm not certain of what you're asking. If you want to know what column and row was selected then it can simply be Target.Row and Target.Column.
So maybe .RefersToR1C1 "=" & Target.Row
Nice that you used code tags, but proper indentation is also very helpful when trying to follow code. Yours is very short and simple, so not a big deal.

 

[Micron]

In case you're still looking for how to combine that into one sub:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names("ActiveRow")
   .Name = "ActiveRow"
   .RefersToR1C1 = "=" & Target.Row
End With

With ThisWorkbook.Names("ActiveColumn")
   .Name = "ActiveColumn"
   .RefersToR1C1 = "=" & Target.Column
End With

End Sub

However, I'm not seeing the need for .Name = line. You've already invoked the named range object by passing the name as a reference to the Names collection. Setting the Name property in the next line when you already know it seems redundant. In that case, this may be all you need:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ThisWorkbook.Names("ActiveRow").RefersToR1C1 = "=" & Target.Row
ThisWorkbook.Names("ActiveColumn").RefersToR1C1 = "=" & Target.Column

End Sub