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
|