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

Post your working scripts, libraries and tools
tmplinshi
Posts: 1591
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: 101
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: 45
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
User avatar
JoeWinograd
Posts: 1592
Joined: 10 Feb 2014, 20:00

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

17 Jan 2020, 13:30

Hi tmplinshi,

I'm trying to convert the following VBA to AHK:

Code: Select all

lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
col = ActiveCell.Column
Range(ActiveCell, Cells(lastRow, col)).Select
n = Application.CountA(Selection)       'Non-Blank cells in the selection
The use of xlCellTypeLastCell resulted in finding this thread...great stuff! I'll be putting xlFindLastCell in my bag-of-tricks!

I know that this question is not exactly related to xlFindLastCell, but it uses the same basic COM Excel features, so I'm hoping that you can help. I converted the first three VBA lines as follows (thanks for the xlCellTypeLastCell enumeration):

Code: Select all

xlCellTypeLastCell:=11 ; enumeration
xl:=ComObjActive("Excel.Application")
lastRow:=xl.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
col:=xl.ActiveCell.Column
ActiveCellAddress:=xl.ActiveCell.Address[0,0]
ActiveRange:=xl.Cells(lastRow,col)
Selection:=xl.Range(ActiveCellAddress,ActiveRange).Select
A MsgBox shows that lastRow and col are correct. Also, the range of cells is properly selected in Excel. However, I'm stuck on the last VBA line, as it's not clear to me what Selection needs to be in AHK for the CountA method. I thought that it would be the value that I assigned to it in the last line posted above, but that doesn't work — n always come back as 1 with this:

Code: Select all

n:=Floor(xl.CountA(Selection))
Thanks for your help. Regards, Joe

Edit on 18-Jan-2020: For others following this thread, the answer to my question is here:
https://www.autohotkey.com/boards/viewtopic.php?f=76&t=71711&p=310222#p310222
Regards, Joe

Return to “Scripts and Functions”

Who is online

Users browsing this forum: stiuna and 27 guests