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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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)
 

WelshGareth

New Member
Joined
Sep 25, 2003
Messages
40

ADVERTISEMENT

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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

WelshGareth

New Member
Joined
Sep 25, 2003
Messages
40
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,488
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,448
Messages
5,572,174
Members
412,447
Latest member
immy
Top