HOW TO: How do I find the last populated cell in a range?

HOW TO: How do I find the last populated cell in a range?

Question

How can I do the equivalent of VBA's Range.End(xlDown)?

Answer

Construct a refence to the cell you want to start at, and then use the CXlRef class and CXlOper::Coerce() to iterate the cell reference until you encounter an empty cell.

The code below will inspect the cells starting with the specified cell reference and moving in the specified direction:

enum {
    xlUp,
    xlLeft,
    xlDown,
    xlRight
};

int CountPopulatedCells(const CXlOper& xloRef, int direction)
{
    if (!xloRef.IsRef())
        return -1;
    CXlOper xloCell = xloRef, xloValue;
    CXlRef xlr = xloRef.GetRef();
    xlr.Right() = xlr.Left();
    xlr.Bottom() = xlr.Top();
    xloCell.GetRef() = xlr;
    int count = 0;
    while (true)
    {
        // Is cell empty?
        if (xloValue.Coerce(xloCell) != 0)
            return -1;
        if (xloValue.IsEmpty())
            break;
        count++;
        // Move to next cell
        if (direction == xlUp)
        {
            if (xlr.Top() == 0)
                break;
            else
                xlr.Bottom() = --xlr.Top();
        }
        else if (direction == xlLeft)
        {
            if (xlr.Left() == 0)
                break;
            else
                xlr.Right() = --xlr.Left();
        }
        else if (direction == xlDown)
        {
            if (xlr.Top() == CXlRef::MaxRow())
                break;
            else
                xlr.Bottom() = ++xlr.Top();
        }
        else if (direction == xlRight)
        {
            if (xlr.Left() == CXlRef::MaxCol())
                break;
            else
                xlr.Right() = ++xlr.Left();
        }
        else
            return -1;
        xloCell.GetRef() = xlr;
    }
    return count;
}

Example

//{{XLP_SRC(RangeEndDown)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(RangeEndDown, "R", "RangeEndDown", "", 
    "User Defined", "Returns the number of populated cells in"
    " the range starting at the active cell and moving down", "",
    "appscope=1\0", 2)

extern "C" __declspec( dllexport )
LPXLOPER RangeEndDown()
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    CXlOper xloRef;
    if (!xloRef.GetActiveCell())
        CXllApp::XlMessageBox("Not a cell reference", XlMessageBoxTypeInformation);
    else
    {
        int count = CountPopulatedCells(xloRef, xlDown);
        char msg[128];
        _snprintf(msg, sizeof(msg), "%d cells are populated", count);
        CXllApp::XlMessageBox(msg, XlMessageBoxTypeInformation);
    }
    return xloResult.Ret();
}

See also

CXlOper::IsEmpty() in the online documentation.
CXlOper::GetRef() in the online documentation.
CXlRef class in the online documentation.