Finding if the activecell is withing a named range

S

Seamus Conlon

Hi,

I have VBA code that is actived with the Worksheet_BeforeDoubleClick event
and I want to
find out if the cell that was double-clicked is within a named range. For
example, if I have a
range named Headers that is defined as A1:H3 and cell B2 is double-clicked I
want to run
the code otherwise I exit the sub.

Any ideas on how to do this?

Thanks.
 
T

Tom Ogilvy

if not intersect(Range("Headers"),Target) is nothing then
' Target is in the range
 
S

Seamus Conlon

Thanks for that, Tom

One further query. After the double-click I want to process cell values
starting in the row below
the last row in the Headers range and on the same column as the cell that
was double-clicked,
(activecell.column). In the example that I gave, this would be cell B4.

Regards,
Seamus
 
T

Tom Ogilvy

a reference to the cell double clicked is Target

Target.Offset(1,0)
is the cell below.
 
S

Seamus Conlon

Ok, but I should have stated that the user may have clicked any one of the
cells
in the header, i.e. B1, B2 or B3, so the one below the target will not
always be
where I want to process from. That's why I wanted to find the last row in
the range.

Thanks,
Seamus
 
T

Tom Ogilvy

Target.column gives you the column of the cell clicked. I am not sure what
the header range is or how to refer to it. If i want the last filled row in
the subject column I would use


set rng = cells(rows.count,target.column).End(xlup)
 
S

Seamus Conlon

Maybe I should explain a bit better?

Let's suppose that Header is a named range of A1: U4 and has labels and
info about the data, which starts at row 5 and currently occupies range
A5: U35. The user clicks on any cell in the header and I want to do various
calculations on that column of data. I don't want to hard code any row
and column numbers so I thought that by using range names I could allow
for insertions and deletions of rows and columns in both header and data.
That's why I want to programmatically find the row at which the data
starts, which I guess would be the starting row of the range named Data
Maybe this is easier to find than the row after the last row in the header
range.

Thanks again,
Seamus
 
T

Tom Ogilvy

It wouldn't be difficult either way

set rng = Range("Header")
rowbelow = rng.rows(rng.rows.count).row + 1


set rng = Range("Data")
rowbelow = rng.rows(1).row


set cell = cells(rowbelow, Target.column)

set col_Data = intersect(cell.EntireColumn, Range("Data")).Cells
mysum = Application.Sum(col_data)
 
S

Seamus Conlon

Thanks Tom, that worked great.

And one final question - I swear. How do I hide/unhide all the rows and/or
columns in the
range named Data?

Thanks,
Seamus
 
D

Dave Peterson

One way:

Dim rng As Range
Set rng = ActiveSheet.Range("data")
rng.EntireColumn.Hidden = True 'False
rng.EntireRow.Hidden = True 'False
 
Top