Page 1 of 1

Excel COM - How to use CountA method in AHK

Posted: 18 Jan 2020, 00:03
by JoeWinograd
Hi Folks,

I'm trying to convert the following Excel 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
That code works fine in Excel VBA, with n getting the number of non-blank cells in the column, beginning with the active cell and going down from there for the rest of the column until the last non-blank cell in any column (not just the active column), and selecting those cells in the active column.

I converted the first three VBA lines to AHK as follows:

Code: Select all

xlCellTypeLastCell:=11 ; enumeration -
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:

Code: Select all

Thanks for your help. Regards, Joe

Re: Excel COM - How to use CountA method in AHK  Topic is solved

Posted: 18 Jan 2020, 01:32
by flyingDman
To use worksheet functions, use something like this:

Code: Select all

so your code should be like this:

Code: Select all

Xl := ComObjActive("Excel.Application") 
msgbox % xl.WorksheetFunction.counta(xl.range(xl.activecell,xl.Cells(lastrow, actcol))) 

Re: Excel COM - How to use CountA method in AHK

Posted: 18 Jan 2020, 11:10
by JoeWinograd
Hi flyingDman,
Thank you very perfectly! Regards, Joe