An enhanced End property of the Range object

tusharm · Apr 6, 2003

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.

Code:

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.

tusharm · Apr 6, 2003

Rudimentary test of boundary conditions with something in A1:

Code:

Sub testFindFirstEmpty()
    MsgBox findFirstEmpty(Range("A1"), xlUp) Is Nothing
    MsgBox findFirstEmpty(Range("A65536"), xlDown) Is Nothing
    MsgBox findFirstEmpty(Range("A1"), xlLeft) Is Nothing
    MsgBox findFirstEmpty(Cells(1, Columns.Count), xlRight) Is Nothing
    End Sub

dk · Apr 7, 2003

Hi mate,

Maybe one change you could make is to change the functions header:-

Function findFirstEmpty(startCell As Range, MoveDirection As XlDirection) As Range

I think that'll make it easier to use the MoveDirection argument than remembering which number is which.

tusharm · Apr 9, 2003

Hi Dan,

I had thought of it except that it doesn't work in XL97.

For 2000+ it's a safety measure worth taking.

Juan Pablo González · Apr 9, 2003

Tushar,

My version of the function (Putting as HTML for readibility, altough harder to copy...)
<pre>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
Dim lRow As Long, lCol As Long
lRow = Abs(MoveDirection = xlDown) + (MoveDirection = xlUp)
lCol = Abs(MoveDirection = xlToRight) + (MoveDirection = xlToLeft)
If IsEmpty(startCell) Then
Set findFirstEmpty = startCell
Else
Set findFirstEmpty = _
IIf(IsEmpty(startCell.Offset(lRow, lCol)), _
startCell.Offset(lRow, lCol), _
startCell.End(MoveDirection).Offset(lRow, lCol))
End If
End Function</pre>But, if I understood you and the idea, this is to prevent the case where the start cell is already empty, right ? but this applies mostly when using the xlDown or xlToRight values, because using xlUp or xlToRight to tend to assume that the starting cell IS empty (A65536 or IV1), the error happening when those two cells AREN'T empty, because you would end up in a cell that's not the last one...

Another thing, in your first test, I got TRUE because there's nothing up of A1, but this worked:

?Range("A1").End(xlUp).Address
$A$1

and I would think, IMO, that its the correct result, I mean, its like trying to press Ctrl Home when you're already in A1, I wouldn't expect an error, just the cursor to stay there...

Waiting for your comments...

tusharm · Apr 9, 2003

Hi Juan,

It's to prevent a bunch of things.

* The starting cell might be empty, in which case it should be returned (but End doesn't).
* The cell next to the starting cell (in the direction of interest) might be empty, in which case it should be returned (but End doesn't).
* If all cells from the starting cell to the last cell (in the direction of interest) are in use, the use of .Offset() returns an error.

I did consider your variant of the code. I actually coded and tested it. However, there were two trade offs to consider. First, performance wise, I'm not sure which wins out: two variables, associated assignments and function calls, but shorter code vs. zero variables, longer code, and a Select Case.

But, the second reason was the overriding factor. The compact version is more difficult to understand

So, given no provable performance benefits and a distinct comprehension advantage, the longer version won out.

Edit: One more point, Juan. You are correct that End works as intended. I'm not trying to fix any problem with it. However, most of the time people use End not just to duplicate the functionality of CTRL+arrow-key; rather, it is to find the first empty cell, which is CTRL+arrow-key and then 'next cell' in the direction of interest. In code, the last bit becomes OFFSET() and that is what faults if one is already at the worksheet boundary -- as in my first example, where A1 contains data and one is trying to find an empty cell going up (or left).

tusharm · Apr 10, 2003

A follow up, Juan,

Actually, the code I had for the compact version was slightly different

Code:

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
    Dim RowVal As Long, ColVal As Long
    If IsEmpty(startCell) Then
        Set findFirstEmpty = startCell
    Else
        Select Case MoveDirection
        Case xlDown: RowVal = 1
        Case xlUp: RowVal = -1
        Case xlToRight: ColVal = 1
        Case xlToLeft: ColVal = -1
            End Select
        Set findFirstEmpty = _
            IIf(IsEmpty(startCell.Offset(RowVal, ColVal)), _
                startCell.Offset(RowVal, ColVal), _
                startCell.End(MoveDirection).Offset(RowVal, ColVal))
        End If
    End Function

I prefer the above code for two reasons. Setting the value of RowVal/ColVal requires 2.5 comparisons (on average) and 1 assignment compared to 4 comparisons, two ABS function calls, two additions, and two assignments. The other, more important, reason (and you'll find I often make this the overriding factor) is readability. The above code is 'more transparent' than the version that requires the reader to be aware of the rules for implicit conversion from boolean to number.

Also, I'm always worried that the vendor will make a change to the underlying language architecture and mess up code that relies on implicit conversions. For example, does anyone know what is the numeric equivalent of TRUE in VB.Net? Is it the same as VB (and VBA), i.e., -1, or has it become +1?

Juan Pablo González · Apr 10, 2003

Ok, nice reasons Tushar

I agree with the readibility factor, altough with this code, there isn't THAT much of a difference... and I guess I have no other comments, except that I feel that this is a good function to use when going from up to down (xlDown) or from Left to right (xlToRight) because using

?findFirstEmpty(Range("A65536"), xlUp).Address, findFirstEmpty(Range("IV1"), xlUp).Address

there are 99.999% chances that I will always get
$A$65536 $IV$1

About implicit conversions, yes, it's a risk, but then, so it's everything with MS ! I mean, they could in Office 12, 13, or whatever, decide that Excel AND VB should return the same value for TRUE, and change Excel's to -1 !

dk · Apr 10, 2003

tusharm said:
A follow up, Juan,
Also, I'm always worried that the vendor will make a change to the underlying language architecture and mess up code that relies on implicit conversions. For example, does anyone know what is the numeric equivalent of TRUE in VB.Net? Is it the same as VB (and VBA), i.e., -1, or has it become +1?

Well, I've just got myself a copy of VB.Net (but have no idea what to do with it yet). Anyway, this simple procedure:-

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MsgBox(CLng(True))
End Sub

produces -1. Guess we're safe for now

tusharm · Apr 10, 2003

Juan Pablo G. said:
Ok, nice reasons Tushar

Glad they make sense

Juan Pablo G. said:
About implicit conversions, yes, it's a risk, but then, so it's everything with MS ! I mean, they could in Office 12, 13, or whatever, decide that Excel AND VB should return the same value for TRUE, and change Excel's to -1 !

One reason I rarely (though, unfortunately, with increasing frequency) rely on implicit conversions, be it how a date is stored in XL or how a boolean is implemented.

A common reason for implicit boolean conversions is to conditionally operate on a list. For example with a list of 3 columns, suppose one wants to sum values from col. 3 only when certain conditions are satisfied by columns 1 and 2. One way to do that is the array formula =SUM((A2:A13<>1)*(B2:B13="nsc")*(C2:C13)). An alternative, longer but one that does not rely on implicit conversions, is =SUM(IF(A2:A13<>1,IF(B2:B13="nsc",C2:C13,""),""))

An enhanced End property of the Range object

tusharm

MrExcel MVP

Excel Facts

tusharm

MrExcel MVP

dk

MrExcel MVP

tusharm

MrExcel MVP

Juan Pablo González

MrExcel MVP

tusharm

MrExcel MVP

tusharm

MrExcel MVP

Juan Pablo González

MrExcel MVP

dk

MrExcel MVP

tusharm

MrExcel MVP

Similar threads

Forum statistics

Share this page

An enhanced End property of the Range object

MrExcel MVP

Excel Facts

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock