First blank cell at the bottom of a column of data

Thanks to Charlie Cork for his great book on QP Formulas, Functions and Macros. (Search this forum for: Quattro Pro Formulas,
Functions, and Macros)

I wanted to place a value in the first blank cell at the bottom of a column of data, using a macro. (I usually just make macros by recording keystrokes and then modifying the results, but this is a little trickier.)

Of course, he tells you just how to do that on page 124, "How to move to the first blank cell at the bottom of a column of data."

I don't completely understand the syntax of the @offset command:
{SelectBlock @OFFSET(A1,@COUNT(A:A),0)}

The Help file for QP Functions gives syntax for @offset as:
@OFFSET(Reference, Rows, Cols, <Height>, <Width>)

But in the example (which works), it looks like Cols comes before Rows.
Is the Help file wrong?

Ron S.

  • Ron, @OFFSET returns a cell address by taking a starting cell (which the help file calls a "Reference") and then determining the cell that is offset from that starting point by the number of rows specified in the second argument in a downward direction, and that is offset by the number of columns specified in the third argument to the right.

    The second argument in the example on page 124 counts the number of non-blank cells in the A column. So where column A consists of continuous series of data entries, the @COUNT function counts the number of entries in the column, which equals the number of rows to offset from the starting point (A1), which is thus the first blank cell.

    The use of the @OFFSET function in this context is more fully explained on page 91; it is addressed more generally in chapter 5. Do note that the function in the macro on page 124 would need to be written differently if there might be blanks in column A before the last data entry in that column.

Reply Children
No Data