An enhanced End property of the Range object

[tusharm]

A popular way to find the first empty cell starting with a predetermined cell is to use the End method of the Range object. For example, to find the first empty cell going down a column, one would use StartCell.End(xlDown).

However, the End property has some serious limitations. If the StartCell is empty, or if it is the only cell with data (in the direction of 'travel'), or if it is already at the worksheet boundary (again, in the direction of 'travel'), the results of the End property will be wrong or a fault will occur.

The enhanced findFirstEmpty addresses those concerns. Comments on the code are welcome -- as are improvements.

Function findFirstEmpty(startCell As Range, MoveDirection As Long) _
        As Range
    On Error Resume Next
    'The above traps boundary conditions and returns an empty range _
     A boundary condition is the StartCell being at the extreme _
     position in the direction specified or the row/column being _
     full in the specified direction
    If IsEmpty(startCell) Then
        Set findFirstEmpty = startCell
    Else
        Select Case MoveDirection
        Case xlDown
            Set findFirstEmpty = _
                IIf(IsEmpty(startCell.Offset(1, 0)), _
                    startCell.Offset(1, 0), _
                    startCell.End(xlDown).Offset(1, 0))
        Case xlUp
            Set findFirstEmpty = _
                IIf(IsEmpty(startCell.Offset(-1, 0)), _
                    startCell.Offset(-1, 0), _
                    startCell.End(xlUp).Offset(-1, 0))
        Case xlToRight
            Set findFirstEmpty = _
                IIf(IsEmpty(startCell.Offset(0, 1)), _
                    startCell.Offset(0, 1), _
                    startCell.End(xlToRight).Offset(0, 1))
        Case xlToLeft
            Set findFirstEmpty = _
                IIf(IsEmpty(startCell.Offset(0, -1)), _
                    startCell.Offset(0, -1), _
                    startCell.End(xlToLeft).Offset(0, -1))
            End Select
        End If
    End Function

Edit: Corrected errors noted by fellow Microsoft Excel MVP John Green regarding the use of xlLeft and xlRight rather than the recommended xlToLeft and xlToRight.

 

[Juan Pablo González]

This can sound bad, but that's not my intention, ok ? Just preventing here ! :D

Aren't you being a bit paranoic too ? I mean, if we're going to talk safety, then let's drop the array formula, because who knows how long is that going to exist too and lets use

=IF(A2<>1,IF(B2="nsc",C2,0),0)+IF(A3<>1,IF(B3="nsc",C3,0),0)+ ... + IF(A13<>1,IF(B13="nsc",C13,0),0)

just in case ! what I'm saying is, you have to work based on something, assuming stuff, hoping that forward compatibility won't be an issue !

For example, I recently downloaded the ButtonFace's AddIn from Jim Rech baiscally, because I wanted to see what were the image differences between versions, as Stephen Bullen pointed out. There is just no way to cover yourself against MS changing the Face Id's in Office 11 !

I'm not trying to pick a fight, just pointing out some side effects of trying to program/formulate trying to forsee any unexpected change by the good folks at MS...

 

[tusharm]

Don't see it as a fight.

My philosophy is that if something is documented I'll use it. The chances that the vendor will change a documented process is very, very small, and even then, we have grounds for arguing. On the other hand, using an undocumented -- and/or a non-recommended -- approach leaves no grounds for complaining if something changes.

Array formulas are here to stay. MS has documentation to support them.

Some of the popular features -- that rely on bugs(!) ore are specifically not recommended -- include:

* A specific value as an argument in MATCH (I don't remember what it is but it has something to do with a very large number). This works but is documented by MS as a bug! When (if?) MS fixes the problem...

* Use of Application.{worksheet function available in VBA} rather than Application.Worksheetfunction.{function} MS specifically stated at the time it introduced Worksheetfunction that the former would be deimplemented at some point. Yet, people not only use the former but recommend it to others!

 

[Juan Pablo González]

Hey, this is getting insteresting...

You're talking about the MATCH(9.999999999E+307,A:A)... I remember your involvment in that thread.

Now, Application vs Application.Worksheetfunction

I *ALWAYS* use Application, basically, because they have different behaviors ! I mean, if they would produce the same results, all the time, I would gladly use Worksheetfunction, but since it doesn't, I don't !

One simple example is the use of Vlookup in VBA. Using this:
<pre><FONT COLOR="#00007F">Option</FONT> <FONT COLOR="#00007F">Explicit</FONT>


<FONT COLOR="#00007F">Sub</FONT> Test()
<FONT COLOR="#00007F">Dim</FONT> Ans <FONT COLOR="#00007F">As</FONT> <FONT COLOR="#00007F">Variant</FONT>
Ans = Application.WorksheetFunction.VLookup(4, Range("A1:B4"), 2, 0)
<FONT COLOR="#00007F">Debug</FONT>.<FONT COLOR="#00007F">Print</FONT> Ans
Ans = Application.VLookup(4, Range("A1:B4"), 2, 0)
<FONT COLOR="#00007F">Debug</FONT>.<FONT COLOR="#00007F">Print</FONT> Ans
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT></pre>
In this little test, A1:A4 equals =ROW() and B1:B4 =CHAR(A1+64)

In both cases I expect a #N/A error, but the problem is that the first one produces a run time error, 1004, and halts the code, which forces me to use On Error Resume Next (I'm NOT saying that I shouldn't error hanlder :P ), but the second one assigns to Ans the value Error 2042 (xlErrNA), which I can test using

If IsError(Ans) Then

Obviosuly, this happens only when the looked value isn't there, but applies to other functions as well...

My point is, Application.function works more reliably in my opinion than Application.Worksheetfunction.function , which is why I use and recommend it...

 

[tusharm]

Yes, others have made the same point about the differences between the behavior of some of those functions when used directly through the Application object or through the Application.Worksheetfunction object.

All I can say is that if (when?) MS delivers on its intent to deimplement the direct access, there's not much window for complaining how MS messed up its customers.

I don't dispute that there is a difference in behavior. If it is intended then we'd better live with it. If it is not intended then we should get it fixed through a bug report.

The same thing applies to MATCH. If/when MS fixes the bug in the function that lets people use the 9.99999999E+307 trick, there won't be grounds for complaining that MS broke my spreadsheet.