Count used rows in Excel

Hey, I am new to scripting and I have been trying to learn all I can about AppleScript and Excel. I have searched the library for a way to do this task, but I have not found anything.

The small goal is to have AppleScript check each sheet in Excel Workbook for the used rows (rows filled with data).

The larger goal is to take the returned value, and create a new email with the data.

Any help or direction would be very helpful.

Hey There,

Here are a few snippets to get you going.

# Select the used range.
tell application "Microsoft Excel"
	tell active sheet
		select used range
	end tell
end tell

# Get data from the used range.
tell application "Microsoft Excel"
	tell active sheet
		set myDataList to value of used range
	end tell
end tell

# Get data from used range of each sheet in the active workbook.
tell application "Microsoft Excel"
	tell active workbook
		repeat with i in (get sheets)
			set rangeValue to value of used range of i
		end repeat
	end tell
end tell

–
Chris

{ MacBookPro6,1 · 2.66 GHz Intel Core i7 · 8GB RAM · OSX 10.11.1 }

Used range never worked for me; it always returned a ridiculously large value. I believe it sees any cell with a non-default setting of any property as in use, which is usually not what one is looking for. Clearing settings does not help; any cell that was ever changed will remain marked as changed, even when set back to default settings.
I use these to find the last in-use cell in a row or column. Note this finds the last cell with data in a given row or column; you must select the right row/column to get a meaningful result.

-- find last used cell in row
tell application "Microsoft Excel"
	tell active sheet
		-- looking in row 62
		set rowToTest to range "62:62"
		-- get an index number
		set cellCount to count cells of rowToTest
		-- move right to left to the last cell with data
		set cCell to get address of (get end cell cellCount of rowToTest direction toward the left)
	end tell
end tell

-- find last used cell in column
tell application "Microsoft Excel"
	tell active sheet
		-- look in this column
		set columnToTest to range "$D:$D"
		-- get an index for the last "used" cell
		set cellCount to count cells of columnToTest
		-- move up to the first cell with data
		get address of (get end cell cellCount of columnToTest direction toward the top)
	end tell
end tell

When you know the maximum number of rows beforehand you can use this to get the first empty row (that’s probably the most common case)

	tell active sheet to set indexFirstUnusedRow to ((first row index of (get end cell 101 of column 1 direction toward the top)) + 1)

Replace the number 101 with your max row count.
Read more on used range here.

Hey Alastor,

That is not my experience with Excel from Office 2011.

When I delete outside items in the used range it contracts.

Move them around “ it changes appropriately.

Yes “ used range can be problematic under some circumstances, but it can also be very convenient under others.

-Chris

This is really easy:

tell application id "com.microsoft.Excel" to get count rows of used range of worksheet 1

Replace “worksheet 1” with whatever you need to point to the particular sheet.

Make sure your worksheet doesn’t have empty cells with spaces in them. Those will count towards the used range.