xlFindLastCell() - Find last non-empty cell in Excel

Post your working scripts, libraries and tools
tmplinshi
Posts: 1428
Joined: 01 Oct 2013, 14:57

xlFindLastCell() - Find last non-empty cell in Excel

21 May 2016, 01:09

xlFindLastCell.ahk

Code: Select all

xlFindLastCell(objExcel, sheet := 1) {
	static xlByRows    := 1
	     , xlByColumns := 2
	     , xlPrevious  := 2

	lastRow := objExcel.Sheets(sheet).Cells.Find("*", , , , xlByRows   , xlPrevious).Row
	lastCol := objExcel.Sheets(sheet).Cells.Find("*", , , , xlByColumns, xlPrevious).Column

	return {row: lastRow, column: lastCol}
}
Example:

Code: Select all

xl := ComObjGet("D:\Desktop\test.xlsx")
lastCell := xlFindLastCell(xl)
MsgBox, % lastCell.row "," lastCell.column
Remarks: You may think of using the UsedRange or xlCellTypeLastCell methods, but the problem is, they also find the empty cell that has format settings (or something else?). You can download this test.xlsx, and test with the following code.
Spoiler
Last edited by tmplinshi on 11 Jan 2019, 00:52, edited 3 times in total.
JJohnston2
Posts: 193
Joined: 24 Jun 2015, 23:38

Re: xlFindLastCell() - Find last non-empty cell in Excel

21 May 2016, 23:52

So does this find the last cell with something in it, whereas UsedRange finds the last cell equivalent to hitting Ctrl+End in Excel?
JJohnston2
Posts: 193
Joined: 24 Jun 2015, 23:38

Re: xlFindLastCell() - Find last non-empty cell in Excel

22 May 2016, 16:20

Saving this one in case I need it later ;) Thanks for posting
elmo
Posts: 92
Joined: 09 Oct 2013, 09:08

Re: xlFindLastCell() - Find last non-empty cell in Excel

13 Sep 2016, 13:32

Thank you tmplinshi; this is a valuable contribution.
iamwyf
Posts: 7
Joined: 02 May 2016, 20:38

Re: xlFindLastCell() - Find last non-empty cell in Excel

17 May 2017, 03:48

For the same purpose, I tried many times. Thank U!
sv270190
Posts: 43
Joined: 06 Feb 2014, 11:48
Facebook: [email protected]

Re: xlFindLastCell() - Find last non-empty cell in Excel

18 Jun 2017, 11:48

GOOD.
can you modify this so that we get the used range say for eg A1:R828
it will further enhance the usefulness of the script
thanks
S.V. SRINIVASAN
SRIVILLIPUTTUR
TAMIL NADU

Return to “Scripts and Functions”

Who is online

Users browsing this forum: magusneo and 61 guests