Postion of number

[Jeff Wright]

Hello, all !



I have a range (let's say I named it "numbers") from A1:A10. I want to
determine the position of the very first non-zero number in that range. For
example, if cells A1 and A2 have a value of zero, and cell A3 has a value of
22, the number I want to have returned as the position is 3.



Likewise, I need to know the position of the last number in the range. For
example, if cells A8:A10 are zero, and cell A7 has a value of 37, the number
I want returned as the last position of a non-zero number is 7.



Thanks for your help!



Jeff W.

 

[Virginia Morris]

Jeff -

I think the following should work. firstRow is the position of the first
non-zero number; lastRow is the position of the last non-zero number. If
there is only one non-zero number in your list, then firstRow & lastRow will
be the same.

Sub NumberTest()

Dim r As Range, cellCount As Long, iCell As Long, jCell As Long

Set r = Range("Numbers")
cellCount = r.Count
For iCell = 1 To cellCount
If r.Cells(iCell).Value <> 0 Then
firstRow = r.Cells(iCell).Row
lastRow = r.Cells(iCell).Row
Exit For
End If
Next iCell

For jCell = iCell + 1 To cellCount
If r.Cells(jCell).Value <> 0 Then
lastRow = r.Cells(jCell).Row
End If
Next jCell

End Sub

Good luck!

 

[Jeff Wright]

Thanks, Virginia! This macro does the job!

Jeff W.

 

[BrianB]

In the absence of another reply, "quick & dirty" :-
'----------------------------------------------
Sub test()
Dim MyRange As Range
Set MyRange = ActiveSheet.Range("A1:A10")
start = 1
finish = MyRange.Rows.Count
For c = start To finish
If MyRange.Cells(c, 1).Value <> 0 Then
MsgBox ("TopDown : " & c)
Exit For
End If
Next
'-----------------------------
For c = finish To start Step -1
If MyRange.Cells(c, 1).Value <> 0 Then
MsgBox ("BottomUp : " & c)
Exit For
End If
Next
End Sub
'--------------------------------------------

 

[Ron Rosenfeld]

Hello, all !



I have a range (let's say I named it "numbers") from A1:A10. I want to
determine the position of the very first non-zero number in that range. For
example, if cells A1 and A2 have a value of zero, and cell A3 has a value of
22, the number I want to have returned as the position is 3.



Likewise, I need to know the position of the last number in the range. For
example, if cells A8:A10 are zero, and cell A7 has a value of 37, the number
I want returned as the last position of a non-zero number is 7.



Thanks for your help!

You can do it with array-formulas. The formulas should be entered as shown,
but then, instead of hitting just <enter>, hold down <ctrl><shift> while
hitting <enter> and XL will place braces {...} around the formula:

First non-zero:

=MATCH(TRUE,A1:A10<>0,0)

Last number:

=MAX(ISNUMBER(A1:A10)*ROW(A1:A10))


--ron