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' :

[TABLE="width: 196"]
<tbody>[TR]
[TD="class: xl66, width: 131, bgcolor: #D7E4BC"]Time
[/TD]
[TD="class: xl67, width: 131, bgcolor: #D7E4BC"]Count
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #EAF1DD"]12am - 1am
[/TD]
[TD="class: xl69, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EAF1DD"]1am - 2am
[/TD]
[TD="class: xl71, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EAF1DD"]2am - 3am
[/TD]
[TD="class: xl71, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EAF1DD"]3am - 4am
[/TD]
[TD="class: xl71, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EAF1DD"]4am - 5am
[/TD]
[TD="class: xl71, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EAF1DD"]5am - 6am
[/TD]
[TD="class: xl71, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EAF1DD"]6am - 7am
[/TD]
[TD="class: xl71, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]7am - 8am
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]1
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]8am - 9am
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]39
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]9am - 10am
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]111
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]10am - 11am
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]75
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]11am - 12pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]82
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]12pm - 1pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]51
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]1pm - 2pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]69
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]2pm - 3pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]180
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]3pm - 4pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]175
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]4pm - 5pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]167
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]5pm - 6pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]67
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]6pm - 7pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"]14
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]7pm - 8pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]8pm - 9pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]9pm - 10pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]10pm - 11pm
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #EAF1DD"]11pm - 12am
[/TD]
[TD="class: xl73, bgcolor: #EAF1DD"][/TD]
[/TR]
</tbody>[/TABLE]

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.