Excel - find the last non-empty row of a sheet

Question

Excel - find the last non-empty row of a sheet

Hi,

I want to import some data from an Excel sheet into Address Book.

How can I simply get the reference of the last row that is not empty ? (or the first empty row)

(With that information, I can display a progress bar during the import.

I found how to get this number with a repeat loop that checks every cell of every row... - not efficient. I also found how to get the first empty cell of a given column, but this is not useful, because the columns may have empty cells. I know there must be a better way)

Thanks,

Nicolas

emac, Mac OS X (10.4.11), ibook G4 (Mac OS X (10.4.11))

Posted on May 2, 2008 10:17 AM

Reply

Answer 1

Top-ranking reply

casdvm

Level 2

320 points

May 2, 2008 11:55 AM in response to Nicolas Silvestre

Nicolas:

I, too, have searched for the elusive method to obtain this information and have had no success. I now use a version of this script:

--Will return an accurate count of rows with some kind of data in the first column (A)
--It will stop at the first blank row it encounters
tell application "Microsoft Excel"
 repeat with x from 1 to (count rows of active sheet)
 if value of cell 1 of row x = "" then exit repeat
 end repeat
end tell

depending upon which column will ALWAYS have some sort of data there. You can also change the 1 in the repeat line to start at whichever row you want as well, which does speed things up nicely.

I hope this helps,

Reply

Answer 2

May 2, 2008 2:05 PM in response to casdvm

Thank you for your answer,

Alas, I have no column where I can be sure there can be no empty cell.
For example, in the column "first name", I can have contacts with no first name.
I have also contacts with no last name, etc...

I did some research in Excel's dictionary, and found a "current region" property that is exactly what I was waiting for. It extends the selection to a range bordered by empty rows and columns !

The structure of the part of the script looks like this :

set NomDuFichierSource to choose file

tell application "Microsoft Excel"
open NomDuFichierSource
set TheCell to cell 1 of row 1
set TheRange to current region of active cell
select TheRange
set NumeroLigneVide to count of rows of TheRange
end tell

I hope this can help you also ?

Regards,

Nicolas

Reply