Formula to determine first and last cell of a range containing a numerical value (XL2007)

[AOB]

Hi there,

This one's a bit complicated so apologies in advance...

I have a range which looks as per the below. The 'Count' column is populated by an IFERROR / VLOOKUP combo which refers to a pivot table elsewhere in the workbook. For simplicity, let's say this table's range is A1:B25 on a sheet called 'Data' :

Time	Count
12am - 1am
1am - 2am
2am - 3am
3am - 4am
4am - 5am
5am - 6am
6am - 7am
7am - 8am	1
8am - 9am	39
9am - 10am	111
10am - 11am	75
11am - 12pm	82
12pm - 1pm	51
1pm - 2pm	69
2pm - 3pm	180
3pm - 4pm	175
4pm - 5pm	167
5pm - 6pm	67
6pm - 7pm	14
7pm - 8pm
8pm - 9pm
9pm - 10pm
10pm - 11pm
11pm - 12am

<tbody>
</tbody>

Now - I use this data as the source for a column chart. But I only want to chart between the extremities which actually contain a number (i.e., in the above example, I don't want to bother including 12am-7am or 7pm-12am in the chart as the values are all 0 and I don't want to 'squeeze' the good data between 7am and 7pm unnecessarily)

So - I refer to a dynamic named range for the chart's source data and am looking for a decent formula to define the range such that I only get the times of the day which return actual figures.

I had been using the following :

=OFFSET('Data'!$B$1,MATCH(TRUE,ISNUMBER('Data'!$B$2:$B$25),0),0,MATCH(1,NOT(ISNUMBER('Data'!$B$2:$B$25))*((ROW('Data'!$B$2:$B$25)-ROW('Data'!$B$1))>MATCH(TRUE,ISNUMBER('Data'!$B$2:$B$25),0)),0)-MATCH(TRUE,ISNUMBER('Data'!$B$2:$B$25),0),1)

Which works fine - BUT - only if the values in column B are contiguous. So if all my numbers are clumped together between two times, it works fine. The problem is if there is a gap, the formula will only return the first contiguous range of numerically-valued cells.

(i.e. if there is a number at 5am-6am, then nothing until 9am, then more values from 9am onwards, the formula defines the range as just 5am-6am and I lose everything after that in my chart)

I've tried messing around with various combinations - MIN/MAX, INDEX/MATCH etc. - but can't come up with anything which does the trick. Obviously I can't use COUNTA as the column is populated by a formula so Excel sees none of the cells as blanks.

Can anybody suggest a formula to define the dynamic range between the first and last cells in column B which contain a numerical value (regardless of whether there are cells in-between which don't)

As I say, bit complicated (am probably over-complicating to be honest) but I can't fathom how to do it.

Any and all help greatly appreciated!!

Thanks

AOB

 

[nuked]

You could add module with the following code

Option ExplicitFunction Chart_Range() As RangeDim rng As RangeDim ws As WorksheetSet ws = Sheets("Sheet1") 'change as you likeSet rng = ws.Range(Cells(ws.Range("A1").End(xlDown).Row, 1), Cells(ws.Range("A" & ws.Rows.Count).End(xlUp).Row, 1))Set Chart_Range = rngSet rng = NothingSet ws = NothingEnd Function

And then use =Chart_Range() as the formula for your named range.

 

[nuked]

In fact, I think you could probably just use =Chart_Range() as the source for your chart series.

 

[pgc01]

Hi AOB

Try for the named range for the Y values:

=INDEX(Sheet1!$B$2:$B$25,MATCH(TRUE,ISNUMBER(Sheet1!$B$2:$B$25),0)):INDEX(Sheet1!$B$2:$B$25,MATCH(2,IF(ISNUMBER(Sheet1!$B$2:$B$25),1)))

 

[nuked]

Oops, not sure where my new lines whent?!

Option Explicit

Function Chart_Range() As Range
Dim rng As Range
Dim ws As Worksheet

Set ws = Sheets("Sheet1") 'change as you like
Set rng = ws.Range(Cells(ws.Range("A1").End(xlDown).Row, 1), Cells(ws.Range("A" & ws.Rows.Count).End(xlUp).Row, 1))
Set Chart_Range = rng
Set rng = Nothing
Set ws = Nothing
End Function

 

[AOB]

nuked,

Thanks a million for taking the trouble to write up that function! Unfortunately, I can't use .End as I actually have a number of these mini-ranges in that same 'Data' sheet. I'm pretty sure, though, I could easily adapt your suggestion for a more generic return (e.g. taking a range as a parameter etc.) The idea had crossed my mind to write a UDF but my preference was to use a dynamic range. But thanks!

pgc01,

That formula is perfect - exactly what I needed! Thank you very much for taking the effort!

Cheers everybody

AOB

 

[pgc01]

You're welcome. Thanks for the feedback.