Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. ...... |
#5
|
|||
|
|||
Domenic,
This is the one. Works exactly as I needed in all my tests this AM. Thank you. Karl -- Karl "Domenic" wrote: Try... |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. ...... |
#8
|
|||
|
|||
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))) |
#9
|
|||
|
|||
sorry didn't notice the other branches of this thread.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find the largest number in column | Excel Discussion (Misc queries) | |||
how to set up a column in excel so it can auto number | Excel Discussion (Misc queries) | |||
How do I sort a column a unique number? | Excel Worksheet Functions | |||
Write formula to add "1" to each number in a column | Excel Worksheet Functions | |||
how do I find an average number of specific words in a column | New Users to Excel |