Detecting the address of an active range

[ChuckDrago]

Hi everyone,

I am trying to build an app where the users can select a group of cells (row wise). If they click on a specific cell, I can get the row address by using ActiveCell.Row. However, they should be able to click on a cell and drag the mouse down to "activate" any number of cells. In that case, I need to obtain the addresses of the first and last row they chose. Not the foggiest about how to do it. Any help, please?
Much obliged, as usual.
Chuck
PS. The application starts by the user selecting the range of cells and then clicking on a button to fire a macro. Therefore, the range parameters are only held by the clicked area, prior to any VBA action. There I get lost...

 

[Fluff]

How about

Sub ChuckDrago()
   Dim FirstRow As Long, LastRow As Long
   With Selection
      FirstRow = .Resize(1, 1).Row
      LastRow = .Offset(.Rows.Count - 1).Resize(1, 1).Row
   End With
End Sub

 

[My Aswer Is This]

It would be best to tell us what you ultimate Goal is.

But here is a sample script.

Just use selection.

Sub My_Range()
'Modified 2/14/2019 3:18:07 PM  EST
Selection.Value = "Cake"
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Just realised I misread the OP
To get the Address rather than the row number use

Sub ChuckDrago2()
   Dim Add1 As String, Add2 As String
   With Selection
      Add1 = Split(.Address(0, 0), ":")(0)
      Add1 = Split(.Address(0, 0), ":")(1)
   End With
End Sub

 

[ChuckDrago]

Just realised I misread the OP
To get the Address rather than the row number use

Sub ChuckDrago2()
   Dim Add1 As String, Add2 As String
   With Selection
      Add1 = Split(.Address(0, 0), ":")(0)
      Add1 = Split(.Address(0, 0), ":")(1)
   End With
End Sub

Thank you oodles! This code provides also the column, which saves me the extra step of testing for it.
The intended app is a Reservation calendar for the various Conference Rooms we have around. The rows represent time slots (e.g. 8:00 - 9:00, 9:00 - 10:00, etc.) and the multiple rows selection is for the user to select a block of time larger than a single hour. The address location allows me to provide ability to do recursive reservations (e.g. daily, weekly, monthly, given days within period, etc.) all selectable by the user via the initial selection panel.
Again, many thanks!!!

 

[Fluff]

You're welcome & thanks for the feedback