Using VBA to find the cell addresses of a dynamic contigous worksheet range

aquixano · Aug 25, 2017

I need to determine the cell addresses for the four corners of a selected range on a worksheet. The top left and bottom right are easy since that’s how Excel normally defines a range. For instance, C2:E4 defines a nine cell range. I need to be able to also determine the two remaining corners are C4 and E2. The range will be dynamic – both in location and size. I’ve tried searching, but haven’t been able to adequately describe what I want to do in the search. I would appreciate any suggestions. Thanks for reading.

JackDanIce · Aug 25, 2017

Welcome to the board. What code do you currently have that determines the selected range? Or do you mean after a range is selected?

MrKowz · Aug 25, 2017

Welcome to the forums!

How are you identifying the contiguous range? Are you selecting it prior to running the code, or is your code identifying the range?

If you are manually selecting it, this code should return the four corners:

Code:

Public Sub FourCorners()
Dim c1      As String, _
    c2      As String, _
    c3      As String, _
    c4      As String
    
Dim rng     As Range, _
    rngcols As Long, _
    rngrows As Long

Set rng = Selection
rngcols = rng.Columns.Count
rngrows = rng.Rows.Count
    
c1 = rng(1).Address
c2 = rng(1).Offset(0, rngcols - 1).Address
c3 = rng(1).Offset(rngrows - 1, 0).Address
c4 = rng(1).Offset(rngrows - 1, rngcols - 1).Address

MsgBox c1 & ", " & c2 & ", " & c3 & ", " & c4
End Sub

Rick Rothstein · Aug 25, 2017

Assuming you meant for a selected range of cells, give this a try...

Code:

[table="width: 500"]
[tr]
	[td]Sub ForCorners()
  Dim TL As String, TR As String, BL As String, BR As String
  TL = Selection(1).Address(0, 0)
  TR = Selection(1).Offset(, Selection.Columns.Count - 1).Address(0, 0)
  BL = Selection(1).Offset(Selection.Rows.Count - 1).Address(0, 0)
  BR = Selection(1).Offset(Selection.Rows.Count - 1, Selection.Columns.Count - 1).Address(0, 0)
  
  MsgBox TL & "     " & TR & vbLf & vbLf & vbLf & BL & "     " & BR
End Sub[/td]
[/tr]
[/table]

aquixano · Aug 25, 2017

Thanks for the Welcome – learning my way around this Board.
I am modifying some Worksheet Selectionchange code I have to essentially make a decision tree in the macro depending upon whether either the Shift or Ctrl key was pressed when the cell was selected. I’ve used this code before when I trigger macros via a button, but the challenge with the worksheet selection change is that holding the shift key down while it will modify the macro also selects a worksheet range (unintended, but I believe unavoidable). Since I want the triggering cell to be the “second” cell of the range, I can differentiate by using a Select Case for the activecell. Here is the code I have and I have attached the test workbook as it currently stands.

Code:

Public S1 As String
'Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'S1 = ActiveCell.Address
'End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
t = Target.Address
If InStr(t, ":") = 0 Then
    If InStr(t, ",") = 0 Then       'Single cell
        S1 = Target.Address
    Else                            'Non-Contigous cells
        t = Split(Target.Address, ",")
        If t(0) = S1 Then
            S1 = t(1)
        Else
            S1 = t(1)
        End If
    End If
Else
    t = Split(Target.Address, ":") 'Contigous cells
    a = ActiveCell.Address
    Select Case ActiveCell.Address
        Case t(0)
            S1 = t(1)
        Case t(1)
            S1 = t(0)
        Case Else
            'Need code ********************
    End Select
End If
MsgBox S1
End Sub

Thanks for replying JackDanIce, and MrKowz. MrKowz, I plugged your sample code into my workbook and it does what I asked. I should be able to use your code to finish up my desired task. Also thanks for including the Forum Rules / FAQs in your reply – very helpful.

aquixano · Aug 25, 2017

Thank you Rick Rothstein for your code as well – it will also give me what I need. This Board is FAST!

aquixano · Aug 26, 2017

Newbie questions. Is there a way to mark this thread as "solved?" I didn't see it in the rules / FAQs.

Also, assume the "Thank you" button on the left side is the best way to acknowledge helpful people - correct?

I had trouble posting this reply – I kept getting this message, even after I reloaded the page: “Your submission could not be processed because the token has expired. Please reload the window” What am I doing wrong?

Finally, this there a way / view to see only my questions?

Thanks for your help.

Rick Rothstein · Aug 26, 2017

aquixano said:
Newbie questions. Is there a way to mark this thread as "solved?" I didn't see it in the rules / FAQs.

No, this forum does not have a method of marking the thread as "Solved" other than posting a message thanking the person or persons for their answer.

aquixano said:
Also, assume the "Thank you" button on the left side is the best way to acknowledge helpful people - correct?

The "Thank you" button is kind of new... there was only a Like button in the past, now there are both, so it is not totally clear what the distinction is. My guess is that if someone's provides you with some help (such as correcting something you posted or adding additional information to a post to help clarify it or, simply, answering your question, etc.), then the "Thank you" button would be used. On the other hand, if a message contains something you like for some reason (such as posting a formula or piece of VBA code that you find interesting and/or surprising in some way, or offering an answer that is particularly nice in some way such as a detailed and/or insightful explanation of some particular point, etc.), then the "Like" button would be used. Or you can click both buttons if the message was such that they both seemed appropriate in some way. Also, you can click these buttons for a post directed at someone else, but which provided you with information you did not know or contained a surprisingly short or clever formula and/or piece of VBA code that made you sit up and take notice. And, of course, you can click these buttons on more than one message within a single thread if you want. I guess all of the above means there is no clear answer to your question... if the mood strikes you for whatever reason, then click the button or buttons that seem appropriate to you at the time.

aquixano said:
I had trouble posting this reply – I kept getting this message, even after I reloaded the page: “Your submission could not be processed because the token has expired. Please reload the window” What am I doing wrong?

I have never seen nor heard of that one before. I don't think you are doing anything wrong though. This is a busy forum with hundreds of questions asked per day and with lots of visitors reading them... sometimes it is so busy that the forum does not "respond" as timely as you or your browser would like. I have seen other odd messages when this occurs, so perhaps your browser just responded with that message at one of those particularly busy times. That is just a guess, of course.

aquixano said:
Finally, this there a way / view to see only my questions?

At the top of the MrExcel window is a series of hyper links, one of which is labeled "My Profile"... click it. On the next webpage that comes up is a series of tabs... click the one labeled "My Activity" (it probably is the default selected tab) and then click the sub-tab (or it may be a button) labeled with your forum's name on it... that will show you all of the messages you have posted. Each of those message summaries has a link to the full thread on it... click it and it will take you to that thread.

Using VBA to find the cell addresses of a dynamic contigous worksheet range

aquixano

New Member

Excel Facts

JackDanIce

Well-known Member

MrKowz

Well-known Member

Rick Rothstein

MrExcel MVP

aquixano

New Member

aquixano

New Member

aquixano

New Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Using VBA to find the cell addresses of a dynamic contigous worksheet range

New Member

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP

New Member

New Member

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock