Solved

current region

Posted on 2013-06-24
5
206 Views
Last Modified: 2013-06-27
I would like to get the address of the range the user clicks.
Thanks
Book101.xlsm
0
Comment
Question by:Svgmassive
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39273344
Try this code, in the worksheet module


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rgSelect As Range, rgStart As Range, rgEnd As Range

If Len(Cells(Target.Row, 2)) > 0 Then
    Set rgStart = Cells(Target.Row, 2)
Else
    Set rgStart = Cells(Target.Row, 2).End(xlUp)
End If

If Len(Cells(Target.Row, 6)) = 0 Then
    Set rgEnd = Cells(Target.Row, 8)
Else
    If Len(Cells(rgStart.Row + 1, 6)) = 0 Then
        Set rgEnd = Cells(rgStart.Row + 1, 8)
    Else
        Set rgEnd = Cells(rgStart.Row, 6).End(xlDown).Offset(1, 2)
    End If
End If

Set rgSelect = Range(rgStart, rgEnd)

MsgBox rgSelect.Address

End Sub

Open in new window

0
 
LVL 2

Expert Comment

by:Agneau
ID: 39273969
Hello Svgmassive,

Strictly following your tamplate this code should work (I've also attached a spreadsheet with the code already implemented).

Please note that the address of the "colored range" selected will appear in the status bar to avoid a annoying message box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim nTop As Long
    Dim nLeft As Long
    Dim nBottom As Long
    Dim nRight As Long
    
    If Target.Interior.ColorIndex <> -4142 Then
        While Target.Offset(nTop, nLeft).Interior.ColorIndex = Target.Interior.ColorIndex And Target.Column - nLeft > 1
            nLeft = nLeft - 1
        Wend
        If Target.Column - nLeft > 1 Then
            nLeft = nLeft + 1
        End If
        
        While Target.Offset(nTop, nLeft).Interior.ColorIndex = Target.Interior.ColorIndex And Target.Row - nTop > 1
            nTop = nTop - 1
        Wend
        If Target.Row - nTop > 1 Then
            nTop = nTop + 1
        End If

        While Target.Offset(nBottom, nRight).Interior.ColorIndex = Target.Interior.ColorIndex
            nBottom = nBottom + 1
        Wend
        nBottom = nBottom - 1
        
        While Target.Offset(nBottom, nRight).Interior.ColorIndex = Target.Interior.ColorIndex
            nRight = nRight + 1
        Wend
        nRight = nRight - 1
        
        Application.StatusBar = "Colored range selected: " & Me.Range(Target.Offset(nTop, nLeft), Target.Offset(nBottom, nRight)).Address
        
    Else
        Application.StatusBar = False
    
    End If
End Sub

Open in new window

Book101.xlsm
0
 

Author Comment

by:Svgmassive
ID: 39274010
agneau your example uses the cell color but that only work if the cell has color,nutsch example looks at the last number in column f it should be actually be looking at the last date in column b and the and the last number in column f  if there is a missing date eg.last date entry.Thanks
0
 
LVL 2

Expert Comment

by:Agneau
ID: 39274063
Hi Svgmassive,

I'm not sure if I understood your requirements.
In order to get the current region, Excel implements a native property of the range object called CurrentRegion

msgbox Cells(4,6).CurrentRegion.Address

Open in new window


According to Excel documentation a "the current region is a range bounded by any combination of blank rows and blank columns."

Depending on the layout of your spreadhseet this would be the easiest way to code it. However your range examples contain blank columns (C, E, G and H) that prevent the CurrentRegion property to obtain the addresses you need.

I would suggest to redesign the spreadsheet layout to make use of CurrentRegion property, if this is not possible you need to clarify what is the logical organization of your spreadsheet:

Are columns C, E, G, H always empty?
How many "wekends"can exist in a single range?
How many "days" can exist in a single range?
Can more than one row of "emp id" column be empty? (or just one to limite the boundaries of the range)

Regards
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39275153
Here's a quick adjust to my original code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rgSelect As Range, rgStart As Range, rgEnd As Range

If Len(Cells(Target.Row, 2)) > 0 Then
    Set rgStart = Cells(Target.Row, 2)
Else
    Set rgStart = Cells(Target.Row, 2).End(xlUp)
End If

if rgstart.end(xldown).row=rows.count then
    set rgEnd=cells(rows.count,6).end(xlup).offset(1,2)
else
   set rgEnd=cells(rgstart.end(xldown).row-1,8)
end if

Set rgSelect = Range(rgStart, rgEnd)

MsgBox rgSelect.Address

End Sub

Open in new window


Thomas
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
create different version if file exists 1 45
Cannot locate cell 15 43
where can i download QDE (Quick Date Entry) add-in for Excel? 4 23
Custom fill series 12 40
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question