AutoHotkey Community

Posted: **18 Jan 2020, 00:03**

Hi Folks,

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

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

Posted: **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)))

Posted: **18 Jan 2020, 11:10**

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

AutoHotkey Community

Excel COM - How to use CountA method in AHK

Excel COM - How to use CountA method in AHK

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

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