find last cell in column - without VBA

Hi,

Apart from using VBA, is there any way to write a formula that will find the 
last used cell in a column of 15 cells? In the first set below, it would be 
..388, in the second set it would be .133.  Any help would be appreciated.

      0.006
      0.000
      0.036
      0.125
      0.133
      0.000
      0.000
      0.000
      0.000
      0.000
      0.000
      0.000
      0.000
      0.313
      0.388



      0.006
      0.000
      0.036
      0.125
      0.133
      0.000
      0.000
      0.000
      0.000
      0.000
      0.000
      0.000
      0.000
      0.000
      0.000


0
hcgood (53)
8/10/2005 7:24:57 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
506 Views

Similar Articles

[PageSpeed] 36

Harold,

One way, for numbers in A1:A15:

=INDEX(A1:A15,SUMPRODUCT(MAX((A1:A15>0)*ROW(A1:A15))))

HTH,
Bernie
MS Excel MVP


"Harold Good" <hcgood@hotmail.com> wrote in message news:eiZ5yEenFHA.3256@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Apart from using VBA, is there any way to write a formula that will find the last used cell in a 
> column of 15 cells? In the first set below, it would be .388, in the second set it would be .133. 
> Any help would be appreciated.
>
>      0.006
>      0.000
>      0.036
>      0.125
>      0.133
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.313
>      0.388
>
>
>
>      0.006
>      0.000
>      0.036
>      0.125
>      0.133
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>
> 


0
Bernie
8/10/2005 7:46:38 PM
Thanks again, I really appreciate this one, I never thought of combining 
these functions to come up with this.

Harold


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message 
news:uDwswPenFHA.3288@TK2MSFTNGP09.phx.gbl...
> Harold,
>
> One way, for numbers in A1:A15:
>
> =INDEX(A1:A15,SUMPRODUCT(MAX((A1:A15>0)*ROW(A1:A15))))
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Harold Good" <hcgood@hotmail.com> wrote in message 
> news:eiZ5yEenFHA.3256@TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> Apart from using VBA, is there any way to write a formula that will find 
>> the last used cell in a column of 15 cells? In the first set below, it 
>> would be .388, in the second set it would be .133. Any help would be 
>> appreciated.
>>
>>      0.006
>>      0.000
>>      0.036
>>      0.125
>>      0.133
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.313
>>      0.388
>>
>>
>>
>>      0.006
>>      0.000
>>      0.036
>>      0.125
>>      0.133
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>
>>
>
> 


0
hcgood (53)
8/10/2005 8:11:58 PM
One way:

=LOOKUP(2,1/(A1:A15>0),A1:A15)

-- 
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Harold Good" <hcgood@hotmail.com> wrote in message 
news:eiZ5yEenFHA.3256@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Apart from using VBA, is there any way to write a formula that will find 
> the last used cell in a column of 15 cells? In the first set below, it 
> would be .388, in the second set it would be .133.  Any help would be 
> appreciated.
>
>      0.006
>      0.000
>      0.036
>      0.125
>      0.133
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.313
>      0.388
>
>
>
>      0.006
>      0.000
>      0.036
>      0.125
>      0.133
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>      0.000
>
> 


0
sandymann2 (1054)
8/10/2005 8:43:27 PM
It worked great, thanks,

Harold


"Sandy Mann" <sandymann2@mailinator.com> wrote in message 
news:%23lmDOxenFHA.3960@TK2MSFTNGP12.phx.gbl...
> One way:
>
> =LOOKUP(2,1/(A1:A15>0),A1:A15)
>
> -- 
> HTH
>
> Sandy
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
> "Harold Good" <hcgood@hotmail.com> wrote in message 
> news:eiZ5yEenFHA.3256@TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> Apart from using VBA, is there any way to write a formula that will find 
>> the last used cell in a column of 15 cells? In the first set below, it 
>> would be .388, in the second set it would be .133.  Any help would be 
>> appreciated.
>>
>>      0.006
>>      0.000
>>      0.036
>>      0.125
>>      0.133
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.313
>>      0.388
>>
>>
>>
>>      0.006
>>      0.000
>>      0.036
>>      0.125
>>      0.133
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>      0.000
>>
>>
>
> 


0
hcgood (53)
8/10/2005 9:36:18 PM
Reply:

Similar Artilces:

show the value of the last point of every series, in Excel line ch
I would lie to show the value of only the last point of all series in a line chart in excel. I can do it manually, (click the point in the graph - data labels - show value). Is it possible to do it automatically, so when I change the data source, the chart will still show the value of the last point ? Hi Assaf, try doing it again after starting the macro recorder... then Excel should give you some hints how to realise it... if your data range varies in length, you can use something like ..Range("A2", "A65534").SpecialCells(xlCellTypeBlanks).Cells (1).Row - 1) to ge...

Cell data overruns into adjacent cell(cells).
I have one entry that is drastically longer than the data that follows, and I want it to overrun into the adjacent cells. It used to, but now it won't. I've tried left justification, but it doesn't seem to help. Any ideas? Whether you want or don't want it to run over, you can probably accomplish this by right clicking the cell, column or row and select Format Cells and under Alighment see if Wrap Text is checked. Just a thought. ...

Is it possible to timestamp a cell?
Is there a function available [not a macro] to timestamp a cell when an entry is made into another cell? Regards No, one of the basics of spreadsheet software is that you cannot update the content of another cell with a fixed value when you modify a cell. Only a macro can do it. newman wrote: >Is there a function available [not a macro] to timestamp a cell when an >entry is made into another cell? > >Regards Newman, This formula, in D2, will latch the current date-time in D2 when an entry is made in A2. If A2 is changed, it won't change the time -- it will still show wh...

How is it that a cell can transfer the correct formula, but the w.
When we copy and paste a cell that has a formula in it, it is transferring the row adjusted formula, but the value from the cell that it was copied from. The settings in paste special are all correct. Any suggestions? What's the formula? What's the result? What do you expect? Regards, Peo Sjoblom "kbigs" wrote: > When we copy and paste a cell that has a formula in it, it is transferring > the row adjusted formula, but the value from the cell that it was copied > from. The settings in paste special are all correct. Any suggestions? The formula is a simpl...

ATTN:Paul B
Hi Paul Thanks for your reply - it seems to be what I'm looking for. However I'm no expert with Excel. Could you please tell me how I go about getting the coding you sent into the VB editor as I've never had cause to use this before. Thanks for your help - it's much appreciated. Cheers Paul Bardsley J Right click on the sheet tab, view code, insert the code. Suggest you paste the code as Paul supplied it so you will see color turn red indicating a syntax error caused by splitting a line without using the break character which is space then underscore. ...

Excel VBA
Hi Excel VBA'ers, Via Google I found some great code (see below) that enables MouseDown to paste what's in clipboard. I want this code to apply to ALL of the textboxes in my Userform. Instead of repeating this code multiple times for every textbox, I was wondering how/if I can specify multiple textboxes using the one instance of the code please. (Hmm, if I'm on the right track, perhaps I have to learn about Class Modules to do this??) Here is the code that I found: Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Sing...

excel 3 column merge
I have an excel file with 4,000 rows and 15 columns. I need to merge three columns so I created a new column for the merged data. Entered formula =M1&","&N1&"/"&O1 and that worked as intended for first row. How do I apply formula to remaining 3,999 rows? One way: Copy the first cell. Select the remaining cells in which the formulae are to appear (i.e., in a column). Paste. In article <5EA9FC86-5D1D-4F5E-A523-22A55DE58519@microsoft.com>, flaglernet <flaglernet@discussions.microsoft.com> wrote: > I have an excel file wit...

edit number in cell without it changing to text
I recently upgraded from Office 97 to Office 2000. In a worksheet I was previously able to hit F2 to edit a number, hit the + or - to add or subtract from the original number. The revised total would show when I hit enter. Now the number changes to text and shows as 1+1 instead of 2. There needs to be an equal sign preceeding the 1+1. After hitting F2, hit the home key to put the curson on the left of the existing number value in the cell then enter "=1+" so you end up with "=1+1" (e.g.). - Will "rklremote" wrote: > I recently upgraded from Office 9...

Counting Blank Cells
H16 I16 J16 $25.2 $33.8 The following formula counts the number of dollar values in H, I and J. =IF(H16="","",IF(M16>0,COUNT(H16:J16))) [answer =2] Without putting $0.00 in cell J16 how would I write a formula to count all three cells? [answer = 3] It depends on the requirement. Will J16 ever contain an actual value of 0? Are the values always positive? Do you want to count J16 even if H and I are empty? >=IF(H16="","",IF(M16>0,COUNT(H16:J16))) You don't have a value_if_false argument de...

where Can I find the download for excel viewer for windows me
where Can I find the download for excel viewer for windows me? It was on the office update page not long ago. Now to download the Excel viewer, windows 2000 or XP is required. Is it possible to view excel files with win me? http://redirx.com/?g9u0 http://www.microsoft.com/downloads/details.aspx? FamilyID=4EB83149-91DA-4110-8595- 4A960D3E1C7C&displaylang=EN >-----Original Message----- >where Can I find the download for excel viewer for windows me? It was on the >office update page not long ago. Now to download the Excel viewer, windows >2000 or XP is required. Is it poss...

add days without adding weekend
Hi Trying to add 4 days to date in cell A1, without counting weekend and if poss bank holidays. in cell C1 example cell a1 = date cell b1 = time If time after 1600 hrs needs to be classed as next day. Then i have to add 4 days to that new date any help appriciated thanks in advance brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200603/1 =WORKDAY(A1,IF(B1>TIME(16,0,0),5,4)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "brian thompson3001 via OfficeKB.com" <u15682@uwe> wrote in message news:...

In Macro: return column value of active cell
Using the VB tools in Excel 2003, am trying to return the column value of the active cell. Any suggestions? Hi Jim col_no = ActiveCell.column Frank Jim wrote: > Using the VB tools in Excel 2003, am trying to return the > column value of the active cell. Any suggestions? Jim, In the immediate window ?ActiveCell.Column -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jim" <anonymous@discussions.microsoft.com> wrote in message news:74d001c3e771$35a5e0d0$a001280a@phx.g...

Export number without decimals
hI, When I export my data from a query in csv format, one of the fields which has 13 digit numbers diplays decimal places when infact the data in the database does not have any decimals. How can I force this field in my query to format with zero decimals so it exports correctly. Eg. currently doing 1310010010011.00 want to show 1310010010011 Bruce Bruce It might be a bit of a kludge, but you could convert the number to text (CStr()) in a query before exporting the query. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/...

Can I get Button name or Cell Reference
If I have a sheet with 20 buttons (1 on each row) and each button points to the same macro, is it possible for the macro to determine the Row number (Or possibly the Button Name)? Based on the row, the macro will continue updating data on that Row. -- Trefor This is a button from the Forms toolbar? Option Explicit Sub testme() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) MsgBox Application.Caller & vbLf _ & BTN.Name & vbLf _ & BTN.TopLeftCell.Address(0, 0) & vbLf _ & BTN.Caption End Sub A...

can i somehow put two cells into one column or split a column
Data>text to columns to split =A1&B1 in a third cell with concatenate 2 (or more) -- Regards, Peo Sjoblom (No private emails please) "Chrono" <Chrono@discussions.microsoft.com> wrote in message news:E211310C-11BC-423A-92AF-5F0863940E4D@microsoft.com... > ...

Pivot table with data from separate columns?
Hi. I want to make a pivot table using two columns from the same table/worksheet. However, the two columns are not not next to each other. How do I tell the Pivot Wizard to use data from these separate columns? Thanks! Ken Select the whole range (all the columns in that range) and just use the columns you need. You will have to make sure that each header is non-blank, though. kk_oop@yahoo.com wrote: > > Hi. I want to make a pivot table using two columns from the same > table/worksheet. However, the two columns are not not next to each > other. How do I tell the Pivot W...

When I insert a formula into a cell, it disappears
heres what is happening: the formula is correct, but when i paste it, the value computes to 0, which is not the correct value. However, if i paste it then press F9, the correct calculation appears but the formula disappears, leaving only the computed value. I need the formula to stay and compute the correct value. Any help would be GREATLY appreciated right now. Thanks Maybe it is an array formula, so try committing it with Ctrl-Shift-Enter rather than just Enter. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "paul" <...

Copy the contents of a range of cells to a single comment (indicator)?
How do you copy the contents of a range of cells to a single comment (indicator)? I have a range of cells ("n12:n14"), and want to copy the contents into a single comment indicator within the activecell. anybody help? Thanks Mik On 29 Apr, 23:11, Mik <mhol...@safetysystemsuk.com> wrote: > How do you copy the contents of a range of cells to a single comment > (indicator)? > > I have a range of cells ("n12:n14"), and want to copy the contents > into a single comment indicator within the activecell. > > anybody help? > > ...

Spreadsheet column headings appear on the wrong line
When I view a spreadsheet on the screen, the column headings appear to be in the cell below where they belong. I am using Office XP. I have re-installed it and have installed all the updates. Molly Column Headings being the A, B, C through IV? Perhaps row 1 is hidden? Gord Dibben Excel MVP On Tue, 20 Sep 2005 10:26:16 -0700, "Molly" <Molly@discussions.microsoft.com> wrote: >When I view a spreadsheet on the screen, the column headings appear to be in >the cell below where they belong. I am using Office XP. I have re-installed >it and have installed all th...

Display value of a VBA variable inside a cell
Hi, I am using Excel 2007. I have created a VBA module called MyModule. Inside this module, I have defined a public variable as follows: Public blnMyBoolean As Boolean Next, I created a Function to return the value of this variable as follows: Public Function GetMyBoolean() As Boolean GetMyBoolean = blnMyBoolean End Function Next, I used my function as a formula for a cell on one of my worksheets as follows: =GetMyBoolean() The formula in this cell works, but it does not update automatically. If my code changes the value of blnMyBoolean, the only way I can s...

Grouping Columns
Using Excel 2000 in a Win 2000 O/S, is is possible to group columns individually? Currently if I group, for example, column C, so that I can collapse it, when I group column D, it gets joined to column C's group. I want to be able to collapse and expand columsn separately. -- LPS Maybe you could insert a very skinny column between C and D (not hidden, though). LPS wrote: > > Using Excel 2000 in a Win 2000 O/S, is is possible to group columns > individually? Currently if I group, for example, column C, so that I can > collapse it, when I group column D, it gets joined ...

Auto Fit Column
I can select a column and have it auto fit. However when I go to the next cell in the column and it has more characters I have to again go to auto fit. Is there a way that the column can be set to automatically auto fit when the characters are greater? Thanks Burt Hi Burt, Select the entire column then use AutoFit. AutoFit will not work well with merged cells. If you have merged cells look at my xlindex.htm page for Jim Rech who has a macro to help with that. If you have wrap turned on and you want the column a little wider, first expand it a bit to what you think looks good then ...

identify similar cell values
I have a column in a spreasheet which has about 10000 values. I jus want to identify cells which have similar values. For eg if 1 0r mor cells have the same value, I would like to identify those cells. Wha function should I be using. Thanks in advance -- Message posted from http://www.ExcelForum.com Hi, You might want to consider using a helper column. For instance if your data is all in column I, insert a helper column in J and copy this formula down in column J. =IF(COUNTIF(I$1:I1,I1)=1,"Unique","Duplicate") See also Chip Pearson's website for more info: http:...

Search cells with many numbers a few numbers?
In Excel, I'd like to search cells which have many numbers in each, they are separated by spaces. I'd like to look in the cells for a few numbers. The logic statements do not seem to work. How do I do this? ...

Splitting text cells without a common denominator
I have a list that contains various names that I need to simplify. e.g. "Best Business Company Limited" shortens to Best Business. However, I also have names that start "A B Best Business" (shorten to A B Best), or "Best A B Business" (Best A B). Does anyone have any suggestions please. Hi don't think this is possible as you don't have a rule which could be implemented in a program. What is the logic why you want to skip 'Buisness' in the second example? If you have a list of all words which could e omitted then this is possible. e.g. delet...