Results 1 to 15 of 18

20101112, 11:26 #1
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
Hi,
I need to find the most efficient function in vba for returning a reference to the first nonzero nonblank cell in a range.
At the moment I am looping through the cells and checking each one, but I would like to use some other method using FIND, MATCH, INDEX and other functions.
cheers!
Dominic

20101112, 13:00 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about this?
It looks in the cells with formulas and then the cells with constants and sees of the 2 which has the lowest row...
Steve
[codebox]Option Explicit
Sub NonBlankNonZero()
Dim rng As Range
Dim rCell As Range
Dim rForm As Range
Dim rConst As Range
Dim lMaxRow As Long
Dim lRowForm As Long
Dim lRowConst As Long
Dim rFirst As Range
lMaxRow = Rows.Count + 1
On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
lRowForm = lMaxRow
If Not rng Is Nothing Then
For Each rCell In rng
If rCell <> 0 Then
Set rForm = rCell
lRowForm = rCell.Row
Exit For
End If
Next
End If
On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
lRowConst = lMaxRow
If Not rng Is Nothing Then
For Each rCell In rng
If rCell <> 0 Then
Set rConst = rCell
lRowConst = rCell.Row
Exit For
End If
Next
End If
If lRowForm < lRowConst Then
Set rFirst = rForm
Else
Set rFirst = rConst
End If
MsgBox "The first cell is " & rFirst.Address
End Sub[/codebox]

20101112, 13:10 #3
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
Hi,
yes I can see how this is more efficient than literally looking through all the cells and checking each one against "" and 0.
I was wondering if it's possible without looping though and doing some clever index match thing.
cheers
Dom

20101115, 08:35 #4
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
aww I was so looking forward to the answer on this one

20101115, 10:35 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,326
 Thanks
 3
 Thanked 215 Times in 198 Posts
How do you define 'first'? Which direction are you going in  down then across, or vice versa?
Regards,
Rory
Microsoft MVP  Excel

20101115, 10:41 #6
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
ok, in this case I have a range which is a single row from left to right. Starting in the leftmost column, I need to find the first nonblank nonzero cell in the row, moving to the right.

20101116, 05:18 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,326
 Thanks
 3
 Thanked 215 Times in 198 Posts
Then something like this (assuming rng and rngFirstCell are your range variables):
Code:if isempty(rng(1)) then set rngFirstCell = rng(1).End(xltoright) Else set rngFirstCell = rng(1) End If
Regards,
Rory
Microsoft MVP  Excel

20101116, 08:35 #8
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
doesn't this just find the first nonblank cell? In my case, I need to find the first cell that isn't blank and also whose value is not zero (and preferably is a number).
I have found several solutions for the first nonblank cell using similar things to what you describe, but I was trying to find the first nonblank nonzero value cell without using a loop, but instead using builtin functions that are more efficient.

20101116, 08:55 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,326
 Thanks
 3
 Thanked 215 Times in 198 Posts
Sorry  I overlooked the nonzero part. You can do it without looping if you use evaluate:
evaluate("MIN(IF(ISNUMBER(" & rng.address & ")*(" & rng.address & "<>0),COLUMN(" & rng.address & ")))")
would give you the column number.Regards,
Rory
Microsoft MVP  Excel

20101116, 10:31 #10
 Join Date
 Oct 2007
 Posts
 172
 Thanks
 0
 Thanked 2 Times in 1 Post
ahh that's the ticket, thanks  although it's not really a pure VBA solution tho

20101116, 12:16 #11
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,326
 Thanks
 3
 Thanked 215 Times in 198 Posts
If you want pure VBA, you have to loop, I think.
Regards,
Rory
Microsoft MVP  Excel

20101116, 22:45 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The looping is more efficient since it can stop when the first nonzero is determined. The formula you list must evaluate and check every cell in the range, even after a nonzero is found and also do comparisons to find the minimum row. But it is a formula based system so on some level is more efficient than a purely VB solution. Have you tested this with large datasets to see if you can see a difference in performance?
Steve

20101118, 10:03 #13
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post

20101118, 10:42 #14
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post

20101118, 19:00 #15
 Join Date
 Dec 2009
 Location
 Sacramento, CA, USA
 Posts
 116
 Thanks
 7
 Thanked 4 Times in 4 Posts