Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    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 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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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]


  3. #3
    2 Star Lounger
    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

  4. #4
    2 Star Lounger
    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

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    How do you define 'first'? Which direction are you going in - down then across, or vice versa?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    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 left-most column, I need to find the first non-blank non-zero cell in the row, moving to the right.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  8. #8
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    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. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Sorry - I overlooked the non-zero 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

  10. #10
    2 Star Lounger
    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

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you want pure VBA, you have to loop, I think.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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 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. #13
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by rory View Post
    evaluate("MIN(IF(ISNUMBER(" & rng.address & ")*(" & rng.address & "<>0),COLUMN(" & rng.address & ")))")
    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?
    Regards
    Don

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by SteveA View Post
    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]
    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]
    Regards
    Don

  15. #15
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Sacramento, CA, USA
    Posts
    116
    Thanks
    7
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by dom_donald View Post
    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 LastLast

Posting Permissions

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