First Empty Row (EXcel)

I found this searching this forum…

count rows of used range

…but it’s reporting back like 10,200+ as the first empty row, not “1385” like it should be.

I was using a repeat loop that just cycled through Column A, row-by-row, until the formula value was empty, but as this spreadsheet gets long, it is starting to take a long time to do it this way.

Help?

tell application "Microsoft Excel"
	
	first row index of (get end (last cell of column 1) direction toward the top)
	
end tell

Are you using the same spreadsheet all the time for this scripts? I ask because I had the same problem a while back. I had to go in the spreadsheet and select the first blank row through like row 10000 and delete. For some reason Excel had hundreds of blank rows that it thought were “used” rows even though they had no data. I seem to remember that I could see where the blank used rows stopped by how they looked when I was scrolling.

Anyway, a little clean up of the spreadsheet itself could solve your problem. But if you are getting spreadsheets from users in questionable states, then you will need to correct for this.

The Used Row of a worksheet is not only the cells that have something in them. If you format a whole column (e.g. C:C rather than C1:C10000) then every row is in the Used range.

Which is a bit hard to “undo” or prevent, given how most people use Excel.

So I’m stuck with the “step through and check for a value” repeat loop? Excel doesn’t have an “empty contents” check?

This is what I’m doing now (Excel X, syntax in Excel 2008 might be different):

			repeat until Formula of ActiveCell is ""
				set currentRow to (currentRow + 1)
				set currentCell to ("R" & currentRow & "C" & currentColumn) as text
				Select Range currentCell
			end repeat

hi
are you looking for something like this

--column "g" is sum of range "a:f"
	set columnToTest to range "g:g" of active sheet -- adjust
	set cellCount to count of cells of columnToTest

	set rowsCount to first row index of (get end cell cellCount of columnToTest direction toward the bottom)
	set usedRanges to used range of worksheet object of columnToTest
	set cella to get count of rows of usedRanges
	set range1 to range "g2"
	set range2 to (get address of (get end cell cellCount of columnToTest direction toward the top))
	set range3 to (get address of range1) & ":" & range2 --used in formula
	set cellb to cella + 1
	set formulaCell1 to "g" & cellb
	set formula of cell formulaCell1 to "=COUNTIF" & "(" & range3 & "," & 172 & ")" --looks for sums of 172

bills

hi
tried my script on column “A”-- ran into your problem,seems if delete shift left or clear is used the blank cells still come up in the usedRange. shift up no problem
fixed the skript

set columnToTest to range "a:a" of active sheet -- adjust
	set cellCount to count of cells of columnToTest
	set range1 to range "a1"
	set range2 to (get address of (get end cell cellCount of columnToTest direction toward the top))
	set range3 to (get address of range1) & ":" & range2 --used in formula 
	set cella to get count of rows of range range3 -- fixes problem
	set cellb to cella + 1
	set formulaCell1 to "a" & cellb
	set formula of cell formulaCell1 to "=COUNTIF" & "(" & range3 & "," & 172 & ")" --looks for sums of 172

hope this helps
bills

Anther approach is to use find to find the last filled cell in a column

This will return the last cell that has a value in column A.
Having the After argument is the top cell in the column and setting the search direction to previous is the “trick”.

tell application "Microsoft Excel"
	set myRange to range "A:A"
	
	find myRange what "*" after (cell 1 of myRange) look in values search direction search previous
	
end tell

If a cell contains a formula that evaluates to “” that cell will NOT be found.
If you want those cells found, Get End (as in post #2) is the method I would use.