Last row of data table varies from month to month

[DFStoneJr]

Here;s something that's bothered me off and on over the years but
that's suddenly become a more acute concern:

I'm importing (using a VBA macro that strips a text file of all its
nonessential characters and row) a general ledger trial balance for
all accounts into a workbook each month and using the data to prepare
financial statements. I use a variety of SUM, SUMIF and array
formulae that refer to my imported data, which take the form of a
database, one record per row.

The last row of the table varies from month to month, depending on how
many records I have imported. Heretofore, I have used a last-row
number sufficiently large to ensure that all rows are inclued in my
formulae. However, especially with the use of array formulae, that
increases my recalculation time significantly; it's also less than
elegant and requires monthly inspection and monitoring to make sure
the formulae are still valid.

Can anyone suggest a technique or trick so that the last row of the
table is also the last row referenced by my formulae each month, no
matter how large or small my row count may be?

Thanks in advance.

 

[Gerrit-Jan Linker]

Hello,

I have created 4 functions for this end in my LITlib Excel functions
library:
=FirstRow(ColumnNumber)
=LastRow(ColumnNumber)
=FirstColumn(RowNumber)
=LastColumn(ColumnNumber)

These functions find the first and the last populated cell in the row
or column given. You can use these formulas in your spreadsheet to
cater for different data sizes in your worksheet.

You find out more about LITlib at www.oraxcel.com specifically at:
www.oraxcel.com/projects/litlib

Best regards, Gerrit-Jan Linker
www.oraxcel.com