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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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
54,326
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,998
Messages
5,834,805
Members
430,322
Latest member
excelnoobnoob

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
Top