Finding if the activecell is withing a named range

[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.

 

[Tom Ogilvy]

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

 

[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

 

[Tom Ogilvy]

a reference to the cell double clicked is Target

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

 

[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

 

[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)

 

[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

 

[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)

 

[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

 

[Dave Peterson]

One way:

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