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

[WelshGareth]

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

 

[NateO]

Hello,

See the following:

Book1
	A	B	C	D
1	0	3
2	0
3	1
4	22
5	0
6	4
7	0
8	10
9	22
10	33
Sheet2

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

 

[erik.van.geit]

Hi, guys,

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

Hopefully you didn't see my post which I deleted
it looked very nice code and worked very well, but using Nates option many lines were deleted

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

 

[erik.van.geit]

in case no other values are found

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)

 

[WelshGareth]

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 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

 

[erik.van.geit]

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

 

[NateO]

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:

Sub foo()
On Error Resume Next
Cells([MIN(IF(A1:A12000<>0,ROW(A1:A12000)))], 1).Select
End Sub

 

[erik.van.geit]

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

Cells(Application.Max(1, [MIN(IF(A1:A12000<>0,ROW(A1:A12000)))]), 1).Select

 

[WelshGareth]

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

 

[Peter_SSs]

WelshGareth

I think this simple formula will do it:
=MATCH(0,A:A,1)+1

Mr Excel.xls
	A	B	C	D
1	0	3
2	0
3	15
4	23.3
5	0
6	0
7	0
8	23
9
Sheet1