Column number of last column containing a number
 

Trying to figure a formula that will give me the column number of the last
column with data in a range. This is a sliding entry row where there may be
blanks at the begining and middle of the range.

Range is b26:m26 and may be as below:
0 0 1 1 0 1 1 0 0 0 0 0

I am looking to get colum 8 for use in an OFFSET lookup to other data in the
column.

I have tried no end of some of the suggested formulas in this forum but have
not gotten there yet.

Thank you
--
Karl

The only way I know to do it is to use VBA. Hit Alt-F11, right click on your
project (VBA Project (xxxxxxx.xls)) select Insert/Module and paste the
following function in the code window.

Function LastColumn(rng As Range) As Long
Dim ISect As Range
On Error Resume Next

LastColumn = 0
Set ISect = Intersect(rng, rng.Parent.UsedRange)
For Each x In ISect
If (x.Value < "" Or x.HasFormula) And _
x.Column LastColumn Then _
LastColumn = x.Column
Next x

End Function


If you wanted to treat 0's as empty cells you could change

If (x.Value < "" Or x.HasFormula) And _
x.Column LastColumn Then _

To

If (x.Value < "" Or x.HasFormula) And _
x.Column LastColumn And x.Value < 0 Then _


To get the next empty cell for a range you would enter =Lastcolumn(x)+1
where x is the range you want evaluated (b26:m26??).


"Karl" wrote:

Trying to figure a formula that will give me the column number of the last
column with data in a range. This is a sliding entry row where there may be
blanks at the begining and middle of the range.

Range is b26:m26 and may be as below:
0 0 1 1 0 1 1 0 0 0 0 0

I am looking to get colum 8 for use in an OFFSET lookup to other data in the
column.

I have tried no end of some of the suggested formulas in this forum but have
not gotten there yet.

Thank you
--
Karl

Try...

=LOOKUP(2,1/(26:26=1),COLUMN(26:26))

....confirmed with just ENTER

OR

=MATCH(2,1/(26:26=1))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER

Hope this helps!

In article ,
"Karl" wrote:

Trying to figure a formula that will give me the column number of the last
column with data in a range. This is a sliding entry row where there may be
blanks at the begining and middle of the range.

Range is b26:m26 and may be as below:
0 0 1 1 0 1 1 0 0 0 0 0

I am looking to get colum 8 for use in an OFFSET lookup to other data in the
column.

I have tried no end of some of the suggested formulas in this forum but have
not gotten there yet.

Thank you

JMB,

Thanks. Sadly I have never used VBA and on the workbook I can only use
formulas to get there. It is not mine and has a LOT of macros that I would
rather not chance messing up.

Karl
--
Karl


"JMB" wrote:

The only way I know to do it is to use VBA. ......

Domenic,

This is the one. Works exactly as I needed in all my tests this AM.

Thank you.

Karl

--
Karl


"Domenic" wrote:

Try...

After some more testing I had to change the comparison from =1 to <"". The
range I am checking is computed and can be a 0 or higher. The formula returns
a "blank" if no values found.

Thanks again Domenic.
--
Karl

Hi Karl,
stalemate? Wait a minute you're not trying to place a formula
just get the row. Perhaps it is possible.

see formulas in
Lastcell, Reset Last Used Cell
Some Worksheet Function coding for lastcell in column (#formulas)
http://www.mvps.org/dmcritchie/excel...l.htm#formulas
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Karl" wrote in message ...
JMB,

Thanks. Sadly I have never used VBA and on the workbook I can only use
formulas to get there. It is not mine and has a LOT of macros that I would
rather not chance messing up.

Karl
--
Karl


"JMB" wrote:

The only way I know to do it is to use VBA. ......

Karl wrote...
Trying to figure a formula that will give me the column number of the last
column with data in a range. This is a sliding entry row where there may be
blanks at the begining and middle of the range.

Range is b26:m26 and may be as below:
0 0 1 1 0 1 1 0 0 0 0 0

I am looking to get colum 8 for use in an OFFSET lookup to other data in the
column.
....

The following array formula is certain to work.

=MAX(IF(1-ISBLANK(B26:M26),COLUMN(B26:M26)))

sorry didn't notice the other branches of this thread.