Free Microsoft Excel 2013 Quick Reference

HOW? Find and display the last cell in a column with a value > 0

I want to create a formula in a cell that will make that cell display the
value of the last cell in a column with a value greater than 0.
i.e.:
A B C D E
1 500


Post your answer or comment

comments powered by Disqus
I want to create a formula in a cell that will make that cell display the
value of the last cell in a column with a value greater than 0.
i.e.:
A B C D E
1 500 <----
2
3 17
4 75
5 500 <----

I want to write a macro that enters data into the cell following the last
cell in a row, so I need to automatically jump to the last cell, plus one.

How to jump to the last cell in a row?

I need to find a formula to return the address of the last cell in a column
that contains data. The location will always change as more data is added to
the sheet.

Good afternoon,

I am trying to reference the last cell in a column. My table has rows added to it on a weekly basis and I have a lot of formulas pulling data from this table to summarise the content. My new rows must go at the bottom of this table and my formulas do not work with blank cells (so I cannot reference an empty cell at the bottom of the table and insert rows above this.

I have searched in loads of places and the only formulas I can see are ones that use the data in the cell which is not what I need or that use VBA which is far too technical for me.

As a simple example (I wish every formula was this simple):

=SUM(A1:last cell in the table)

I hope I have made myself clear enough (I tend to rant when I don't quite know what I am talking about).

Anyone that could help me over the weekend would be amazing and I'll check the answers when I get back to work on monday.

Thank you so much in advance guys.

I need to build a macro that lets me find the last cell in a row with data in it then move to the next blank cell & past data. The final report is supposed to build on itself finding the last cell without data in it then pasting the current data. Then the same macro can be run again finding the next available cell pasting the next current data & so on & so on. The problem I am having is moving to the next available blank cell without making reference to that cell in the macro. Which means of course that all new data will be pasted to the same location.

Here is a macro I found on another help site. It looks like it may have some aspects that can help me although I don't really understand it or know how to modify it to suit my application.

Const xlCellTypeLastCell = 11

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:ScriptsTest.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate

intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" & intNewRow

objExcel.Range(strNewCell).Activate

Any help would be greatly appreciated

Hello, yesterday i was working on my current excelproject and got stuck as I in some way have to get the value of the last cell in a constantly growing(manual input which generates this column) column. Of course I could just copy + paste the value but I'd rather have som function doing it for me.

Anyone got ideas on how to do this?

Thanks in advance!

without using the "morefunc" add-in function =lastrow(range) does anyone know the best way to find the last cell in a column?

I am wondering if there is a shortcut that will bring me to the last cell in
a column. I tried <edit><go to><special><last cell> but it didn't work. I'm
dealing with a text document that is converted to Excel.

Easy question,

I am trying to display the last entry in a column.
I have dates listed in column A of sheet 2.
Example:

Date
12 Mar 04
13 Mar 04
18 Mar 04

I want a formula to simply find the last date entered in that column and display it in a separate cell on sheet 1.
Example:

Date of Last Flight: 18 Mar 04

Any ideas? Thanks in advance for your help!

-Dan

I have a column of data that starts in E8 and each cell contains the formula =IF(AND(ISBLANK(C9),ISBLANK(D9)),"",SUM(E8+C9+D9)). As you can see from this formula, no data will be displayed unless C9 or D9 have somethign in them. I need a formula in E5 that will find the contents of the last cell in column E that has data displayed. The E5 formula will be in numerous sheets and each sheet will have a different end row in col E with data.

Thanks!

How do I find the last cell in a column that contains data and copy that data to another cell?

Hi all,

I've had a look around the net but haven't been able to find an answer to my question.

I'm trying to write a code to select a range starting at cell B4 and ending at the last cell within the same column which doesn't contain the word "Nil"

In otherwords my spreadsheet contains a number of formulas like so:

B4 =if(A4="","Nil","<text>")
B5 = if(A5="","Nil","<text>")
B6 =if(A6="","Nil","<text>")
etc.

This continues to B20.

The code needs to select from range B4 to the last cell in column B which has an 'if' false value in it - in otherwords find the last cell with Nil in it then offset it by -1. The answers will always be in order starting at B4 so there won't be gaps or any Nil answers between the false answers.

Hope this makes a bit of sense - it's just hard to explain.

Thanks all,

AA

Is there any way of displaying the last cell with a value in it from one column in a fixed cell, without using a macro?

I want to display the last value in column E (which varies in length as it shrinks and grows every hour) in cell K7.

As always, any ideas would be welcome.

How do you find the last cell in a series i.e. in a column? I have heard that
there is a simple command that does it but I dont know what it is. Please
help me!

Hi all,

I am trying to write some VBA code to select a range in a column going down to the last item in the column.

So I want to write a macro that will copy cells B4 - B17 into cells A4 - A17 but am having trouble with the code that will select B4 - B17. When I use the usual code which is as follows:

Code:
it selects down to A75, and I know why this is but not how to fix it.

This is raw data extracted from a third-party system. It has an option to export to Excel, but where it appears to have blank cells Excel actually believes there is data in there.

So is a way to identify what data Excel thinks is in there and perhaps use this to be able to select the range I want?

Different extractions will need a different range moved over - however it will always start at B4 and go down to B-whatever.

Hope you can help and I can send an example spreadsheet if needed...

Thanks
Richard

How do you display the sheet name in a cell?

...I don't know how. I need some help automating a spreadsheet I use in my office. I can work my way around excel pretty good, I just don't know the programming for what I want. This form is for keeping records of invoices and assigning them P.O. (purchase order) numbers.

The first cell would need to populate the date, but only if there is going to be information in the rest of the row.

The second cell would need to count and display the next number in a sequence. It's a 9 digit sequence. The first 3 numbers will never change, then a dash (-), the next two numbers would only change once a year, ex. (08), another dash (-), then a set of 4 digits ranging from 0001 to 9999. This 9 digit sequence is the PO numbers.

Also I want to add a "clear" button so that the form can be reset for new info.

Pull down menus would be nice to add also, but that may more than what I can hope to get done.

Hi!

I'm trying to find out how to do the following:

I want to find the last cell in a row with data in, up to Column K. I want to then copy this cell into Column A.

So its whatever is furthest right between B1 and K1 copied into A1.

I need to do this for all rows in a workbook, meaning that the furthest right cell in each row may not be the same.

I have attached an example workbook if someone would care to take a look.

Thanks!
Dave

How do I get Excel to identify the last number in a column? For example, we have a doorcount where I work. We take the final number at the end of the month and subtract the first number and divide by two in order to get the total number of visitors. The problem I have is that not every month has the same number of days so I need to know how to identify the last day. Any help would be much appreciated.

Thanks,
Rob Robinette

I am very new to VBA and have Microsoft Office Excel 2007 Power programming with VBA in which this website was listed as a good reference site. I am working on a spreadsheet where I want to select the last non-empty cell in a column and the column always has some cells that are empty, though it is NOT the last non-empty cell in the column. This spreadsheet is automatically generated and I want to change this particular column to all one format and all one number, 0042. This spreadsheet is then loaded into another system via tab-delimited txt file, so this number is meant to serve as an indicator. Sounds like I gave too much information, just wanted to be clear. Any help is appreciated. Thanks.

I am running a column of decending and assending numerical values and I am
not sure which cell will contain the last entry. However, when the last
numerical figure posts, I need to move this value to another cell location,
automatically. What function can I use to obtain this result? There are blank
cells between the last entry in the column and the desired cell, of
destination. Rather then Copy & Paste. The location and value of the last
entry in a column may vary from month to month, but it is important to
relocate this amount to a new destination cell. I am unaware of any function
that will give me the desired results.

Can any one tell me how to use a function to find the last cell in a column
with a value in it?

Thanks in advance

Andy

I am running a column of decending and assending numerical values and I am
not sure which cell will contain the last entry. However, when the last
numerical figure posts, I need to move this value to another cell location,
automatically. What function can I use to obtain this result? There are blank
cells between the last entry in the column and the desired cell, of
destination. Rather then Copy & Paste. The location and value of the last
entry in a column may vary from month to month, but it is important to
relocate this amount to a new destination cell. I am unaware of any function
that will give me the desired results.

Hello,

I am trying to display the last date in a column, with an unknown number of rows.

The data that I have to use is automatically downloaded from a server, but has a "Z" at the end e.g. "2010-10-04Z".

I have used an INDEX(=INDEX(,COUNTA(),1) formula to display the last date but when I first remove the "Z" with a LEFT(,LEN()-1) formula, it doesn't work.

I would be really gratefully if anyone can help. The xls is attached.

Thank you,
Casper


No luck finding an answer? You could always try Google.