find the last cell with a value greater than 0

[stevekirk]

hi,

in my macro i need to find the last value in a column there are zero's that have to be ignored

for example

a1 = 5
a2 = 7
a3 = 0
a4 = 6

therefore last cell is A4.

This cell reference could vary depending on how much data in going into column a

i will copy from that last cell across to the column t

thanks

steve

[starguy]

you can do this with formula.
put following function into a cell where you want to return the last value greater than 0

=LOOKUP(2,1/(A1:A5>0),A1:A5)
you can extend this range (A1:A5) according to the length of your data.

Regards.

hi,

in my macro i need to find the last value in a column there are zero's that have to be ignored

for example

a1 = 5
a2 = 7
a3 = 0
a4 = 6

therefore last cell is A4.

This cell reference could vary depending on how much data in going into column a

i will copy from that last cell across to the column t

thanks

steve

[skatonni]

See if you can use something like this.

Please Login or Register  to view this content.

[stevekirk]

hi,

thanks macro works great. had to modify the range select part it needed to go just below the varibles. ?

i cannot understand how it finds the cell with the number in it
i was checking the varibles as i had iot in step mode but could never see hoe thid bit worked

thanks very much

steve

[skatonni]

It is basically finished here:

Please Login or Register  to view this content.

Since there is no selecting in the loop (until the end) you cannot see cells being selected when you step through.

If you have not already done this then

Set up a test sheet where there are more entries in column A than column B.

Temporarily add two debug.print statements.

Please Login or Register  to view this content.

Select any cell in column B. Open the Immediate window and step through or run the code to see how the last value in Column A is found.

[pullchalks]

The title is exactly what I'm looking for but this formula does not seem to accomplish this.

Assuming:
A1 = 5
A2 = 7
A3 = 0
A4 = 6
A5 = 0

...this formula =LOOKUP(2,1/(A1:A5>0),A1:A5) yields: 6

Instead, I'm in need of a formula (not VBA) that yields: 4
In other words I need to discover how many rows down has the last cell greater than 0

Thanks

you can do this with formula.
put following function into a cell where you want to return the last value greater than 0

=LOOKUP(2,1/(A1:A5>0),A1:A5)
you can extend this range (A1:A5) according to the length of your data.

Regards.

[MarvinP]

Hi,

I think you are looking for something like this:

=LEN(TRIM(CONCAT(IF(A1:A5>0,"1"," "))))

You need to enter the above using a CSE (Control+Shift+Enter) key combination.

The idea here is to use the Trim function to knock off the spaces (0's) on the end. This will only work if your first cell does not have a zero in it. In VBA you could use the RTRIM() function to only knock off the right most spaces, but normal Excel functions don't have that.

[Phuocam]

=LOOKUP(2,1/(A1:A5<>0),ROW(A1:A5))

[pullchalks]

I didn't get a positive result: "#NAME?" - with or without CSE.

[pullchalks]

=LOOKUP(2,1/(A1:A5<>0),ROW(A1:A5)) yields a result of 5 if starting at A1 but if starting from A5 yields a result of 9. I need to know the last cell in a series with a value greater than 0 (every cell includes a number) regardless of where the series is on the spreadsheet. So...

Assuming:
A11 = 5
A12 = 7
A13 = 0
A14 = 6
A15 = 0

I need a formula (not VBA) that still yields: 4

[Phuocam]

You are welcome.

[pullchalks]

Actually Phuocam, I discovered a shortcoming.

=LOOKUP(2,1/(A1:A5<>0),ROW(A1:A5)) yields a result of 5 if starting at A1 but if starting from A5 yields a result of 9. I need to know the last cell in a series with a value greater than 0 (every cell includes a number) regardless of where the series is on the spreadsheet. So...

Assuming:
A11 = 5
A12 = 7
A13 = 0
A14 = 6
A15 = 0

I need a formula (not VBA) that still yields: 4

Thanks

[Tony Valko]

The forum owner would prefer you start your own thread rather than posting your question in someone else's thread.

See forum rule #2.

http://www.excelforum.com/forum-rule...rum-rules.html

[Phuocam]

Assuming:
A11 = 5
A12 = 7
A13 = 0
A14 = 6
A15 = 0

I need a formula (not VBA) that still yields: 4

Thanks

Edit ...

=LOOKUP(2,1/(A11:A15>0),ROW(A11:A15)-ROW(A11)+1)

[pullchalks]

Perfect

[AliGW]

@Pullchalks - unfortunately, as Tony has said, your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

I see that you have found a solution this time, so will lock the thread. Please do not do this again in future. Thank you.