How to know on which row a value ceases to be zero

WelshGareth

New Member
Joined
Sep 25, 2003
Messages
40
Hi,

There are two relevant columns in a table of thousands. The first has the row count, the cells in the second are zero until a long way down the table when they start containing higher numbers.

At the moment, I have to scroll down until I see a number other than zero to see on which row this happened.

What I'd like is a cell to tell me on which row the value first ceases to be zero. Can anyone think of a way of doing this please?

Gareth
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hello,

See the following:
Book1
ABCD
103
20
31
422
50
64
70
810
922
1033
Sheet2


The function is entered normally, without { }, then array-entered, by hitting Ctrl-Shift-Enter.
 
Upvote 0
Hi, guys,

if you want to select the cell you can use some code

Hopefully you didn't see my post which I deleted :oops:
it looked very nice code and worked very well, but using Nates option many lines were deleted
Code:
Sub goto_first_non_zero()
Dim CellRow As Long
Const col As String = "B"
Const FR As Long = 2    'first row
Const MyValue = 0

CellRow = Evaluate("=MIN(IF(" & col & FR & ":" & col & "12000<>" & MyValue & ",ROW(" & col & FR & ":" & col & "12000)))")
Cells(CellRow, col).Select
End Sub
as you can see three values can be edited
column, first row, value to find

kind regards,
Erik
 
Upvote 0
in case no other values are found
Code:
Sub goto_first_non_zero()
Dim CellRow As Long
Const col As String = "B"
Const FR As Long = 2    'first row
Const MyValue = 0

CellRow = Evaluate("=MIN(IF(" & col & FR & ":" & col & Rows.Count & "<>" & MyValue & ",ROW(" & col & FR & ":" & col & Rows.Count & ")))")
If CellRow <> 0 Then Cells(CellRow, col).Select Else MsgBox "no other values found", 48, "TITLE"
End Sub
also changed 12000 to Rows.Count (last row)
 
Upvote 0
Thanks a lot both.

I've gone with Nate0's solution (adding a '-8' to the end so I get my table row number instead of the spreadsheet row number) and it works :biggrin: I can sort of see what the formula's doing but I think I need to see a few more uses of arrays before I'll 'get it' properly.

I'm afraid the programing option is well beyond me at my present understanding but I appreciate your work Erik in writing me a solution.

Best thing is, my spreadsheet does what I want it to. Thanks again.

Gareth
 
Upvote 0
I'm afraid the programing option is well beyond me at my present
you can do it
step by step
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the code

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)

for your purpose it would be nice to assign a shortcutkey
Menu Tools / Macro / Macros / Options ... you'll see it

hitting the shortcutkey would bring you straight to the cell

best regards,
Erik
 
Upvote 0
You're welcome.

Heh, I did see your first post, Erik. Why burn all of that stuff into Variables/Constants, for a 1-time peak, though?

How about:

Code:
Sub foo()
On Error Resume Next
Cells([MIN(IF(A1:A12000<>0,ROW(A1:A12000)))], 1).Select
End Sub
 
Upvote 0
Heh, I did see your first post, Erik. Why burn all of that stuff into Variables/Constants, for a 1-time peak, though?
it's nice to create code which can easily be edited for future use
the columnletter is appearing 5 times
the first row can be any (appearing twice)
also to avoid changing values within a long formula and to see everything what can be edited on top, I used also a constant for MyValue

taking code from my library, I can see quickly how to change it for the next job
it was my experience that a lot of people ask to change code and I found it easier to refer them to the top of the code, then to explain on which line the change has to be made

best regards,
Erik

EDIT: or
Code:
Cells(Application.Max(1, [MIN(IF(A1:A12000<>0,ROW(A1:A12000)))]), 1).Select
 
Upvote 0
erik.van.geit said:
you can do it
step by step

OK, Erik - you've twisted my arm! I've got it doing what I want now and I'm using it for a few of the columns, but I think I may as well take advantage of your coding - for experience and learning as much as for anything else. I'll probably still start with Nate0's program first as it's simpler but will give yours a whirl too. Not tonight though. It's time for me to call it a day. Cheers both again for your interest and assistance.

Gareth
 
Upvote 0
WelshGareth

I think this simple formula will do it:
=MATCH(0,A:A,1)+1
Mr Excel.xls
ABCD
103
20
315
423.3
50
60
70
823
9
Sheet1
 
Upvote 0

Forum statistics

Threads
1,212,148
Messages
6,106,237
Members
448,008
Latest member
D_SQUARED

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top