# Thread: Get first non-blank non-zero cell in a row

1. Hi,

I need to find the most efficient function in vba for returning a reference to the first non-zero non-blank 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

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]

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

4. aww I was so looking forward to the answer on this one

5. How do you define 'first'? Which direction are you going in - down then across, or vice versa?

6. ok, in this case I have a range which is a single row from left to right. Starting in the left-most column, I need to find the first non-blank non-zero cell in the row, moving to the right.

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

8. doesn't this just find the first non-blank 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 non-blank cell using similar things to what you describe, but I was trying to find the first non-blank non-zero value cell without using a loop, but instead using built-in functions that are more efficient.

9. Sorry - I overlooked the non-zero part. You can do it without looping if you use evaluate:

would give you the column number.

10. ahh that's the ticket, thanks - although it's not really a pure VBA solution tho

11. If you want pure VBA, you have to loop, I think.

12. The looping is more efficient since it can stop when the first non-zero is determined. The formula you list must evaluate and check every cell in the range, even after a non-zero 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

13. Originally Posted by rory
Hi Rory
This morning I'm as sharp as a marshmallow. Would you be so kind as to post a small workbook with an example of this solution?

14. Originally Posted by SteveA
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]
If rCell <> 0 Then[/codebox]
Hi Steve

Thanks for the ingenious code; I will add it to my library.

For the intended use I suggest that the two instances of the line shown above should be revised to:[codebox]
If rCell <> 0 And IsNumeric(rCell) Then[/codebox]

15. Originally Posted by dom_donald
but instead using built-in functions that are more efficient.
Our computers have giga-hertz clocks, gigabytes of RAM, and you want an excel search to be more "efficient"? Son, back in my day, that may have made sense, but not now. The only programs that should be more "efficient" now are long-running numerical programs.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•