Detecting the address of an active range

ChuckDrago

Active Member
Joined
Sep 7, 2007
Messages
470
Office Version
  1. 2010
Platform
  1. Windows
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...
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
How about
Code:
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
 
Upvote 0
It would be best to tell us what you ultimate Goal is.

But here is a sample script.

Just use selection.

Code:
Sub My_Range()
'Modified 2/14/2019 3:18:07 PM  EST
Selection.Value = "Cake"
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Just realised I misread the OP
To get the Address rather than the row number use
Code:
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
 
Upvote 0
Just realised I misread the OP
To get the Address rather than the row number use
Code:
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!!!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top