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

Get help with using AutoHotkey and its commands and hotkeys
User avatar
JoeWinograd
Posts: 1454
Joined: 10 Feb 2014, 20:00

Excel COM - How to use CountA method in AHK

18 Jan 2020, 00:03

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 - https://docs.microsoft.com/en-us/office/vba/api/excel.xlcelltype
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:

Code: Select all

n:=Floor(xl.CountA(Selection))
Thanks for your help. Regards, Joe
User avatar
flyingDman
Posts: 675
Joined: 29 Sep 2013, 19:01

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

18 Jan 2020, 01:32

To use worksheet functions, use something like this:

Code: Select all

xl.WorksheetFunction.counta(xl.Range("a1:a3"))
so your code should be like this:

Code: Select all

Xl := ComObjActive("Excel.Application") 
lastRow:=xl.ActiveSheet.Cells.SpecialCells(11).Row
actcol:=xl.ActiveCell.Column
msgbox % xl.WorksheetFunction.counta(xl.range(xl.activecell,xl.Cells(lastrow, actcol))) 
User avatar
JoeWinograd
Posts: 1454
Joined: 10 Feb 2014, 20:00

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

18 Jan 2020, 11:10

Hi flyingDman,
Thank you very much...works perfectly! Regards, Joe

Return to “Ask For Help”

Who is online

Users browsing this forum: AHKStudent, Bing [Bot], Google [Bot], jamesldnaylor, julesverne, omareg94, Spikea, swagfag, teadrinker and 225 guests