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

[tmplinshi]

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

Code: Select all

xl := ComObjGet("D:\Desktop\test.xlsx")

UsedRange := xl.ActiveSheet.UsedRange
rowCount := UsedRange.Row    - 1 + UsedRange.Rows.Count
colCount := UsedRange.Column - 1 + UsedRange.Columns.Count
MsgBox, % rowCount "," colCount

xlCellTypeLastCell := 11
rowCount := xl.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
colCount := xl.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Column
MsgBox, % rowCount "," colCount

xl.close()

[JJohnston2]

So does this find the last cell with something in it, whereas UsedRange finds the last cell equivalent to hitting Ctrl+End in Excel?

[tmplinshi]

Yes.

[JJohnston2]

Saving this one in case I need it later Thanks for posting

[elmo]

Thank you tmplinshi; this is a valuable contribution.

[iamwyf]

For the same purpose, I tried many times. Thank U!

[sv270190]

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

[JoeWinograd]

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