Count cells in Excel column containing values

Get help with using AutoHotkey and its commands and hotkeys
hidefguy
Posts: 57
Joined: 11 Apr 2017, 20:42

Count cells in Excel column containing values

08 Dec 2018, 22:46

I'm trying to create a value in this loop based on counting only cells in column M that contain values (text/numeric). Basically all the used cells.

loop, %CountUsed
{
send, ^{PgUp}
sleep, 1000
}

So if 40 cells in column M have values/used cells, I want to PgUp 40 times.
hidefguy
Posts: 57
Joined: 11 Apr 2017, 20:42

Re: Count cells in Excel column containing values

09 Dec 2018, 23:22

I tried this, but it just hangs up. No errors though

Code: Select all

CountUsed := FileFrom.Sheets(1).UsedRange.Range("M:M")

loop, % CountUsed
{
send, ^{PgUp}
sleep, 1000
}
And this without input reference:

Code: Select all

loop, % FileFrom.Sheets(1).UsedRange.Range("M:M")
{
send, ^{PgUp}
sleep, 1000
}
hidefguy
Posts: 57
Joined: 11 Apr 2017, 20:42

Re: Count cells in Excel column containing values

10 Dec 2018, 17:31

Tried this one too, but got skunked

CountUsed := FileFrom.WorksheetFunction.CountA(Sheets(1).Range("M:M"))

loop, % CountUsed
{
send, ^{PgUp}
sleep, 1000
}
hidefguy
Posts: 57
Joined: 11 Apr 2017, 20:42

Re: Count cells in Excel column containing values

10 Dec 2018, 17:41

This one also. Probably making this harder than it is :)

myRange = Range("M:M")
NumRows = FileFrom..Sheets(1).CountA(myRange)

loop, NumRows
{
send, ^{PgUp}
sleep, 1000
}
awel20
Posts: 190
Joined: 19 Mar 2018, 14:09

Re: Count cells in Excel column containing values

11 Dec 2018, 10:23

hidefguy wrote:Tried this one too, but got skunked

Code: Select all

CountUsed := FileFrom.WorksheetFunction.CountA(Sheets(1).Range("M:M"))
Hey, try this :)

Code: Select all

xlApp := ComObjActive("Excel.Application")
MsgBox % xlApp.WorksheetFunction.CountA( xlApp.Worksheets(1).Range("M:M") )
xlApp  := ""

Code: Select all

xlApp := ComObjActive("Excel.Application")
MsgBox % xlApp.Worksheets("Sheet1").Range("M:M").Cells.SpecialCells(2).Count ; xlCellTypeConstants = 2
xlApp  := ""
hidefguy
Posts: 57
Joined: 11 Apr 2017, 20:42

Re: Count cells in Excel column containing values

11 Dec 2018, 10:41

The first one worked. TY very much awel20
awel20
Posts: 190
Joined: 19 Mar 2018, 14:09

Re: Count cells in Excel column containing values

11 Dec 2018, 10:44

No problem. The second one worked for me too, but you probably need to change "Sheet1" to whatever your sheet is named, or just use 1 like in the other one.
hidefguy
Posts: 57
Joined: 11 Apr 2017, 20:42

Re: Count cells in Excel column containing values

11 Dec 2018, 10:54

Second one is good, but it hung up due to protected workbook.

Would you happen to know how to reference a variable or input within a Range("A1:A100")?
Example:
Gui, Add, Edit, x112 y70 w80 h20 vStartRow,
Range("A%StartRow%:A100")

Not sure how bad I botched that up :)
awel20
Posts: 190
Joined: 19 Mar 2018, 14:09

Re: Count cells in Excel column containing values

11 Dec 2018, 11:48

Here's two ways to do it:

Code: Select all

startRow := 3
xlApp := ComObjActive("Excel.Application")
myRange := xlApp.Range("A" startRow ":A100")

Code: Select all

startRow := 3
xlApp := ComObjActive("Excel.Application")
topCell := xlApp.Cells(startRow, 1) ; Cell A3
botCell := xlApp.Cells(100, 1) ; Cell A100
myRange := xlApp.Range(topCell, botCell)
hidefguy
Posts: 57
Joined: 11 Apr 2017, 20:42

Re: Count cells in Excel column containing values

12 Dec 2018, 00:43

First one worked, first try. Thanks again friend :wave:

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], brotherS, calipsoive, Google [Bot], KiddoV, Rohwedder, tobsto occupied, vsub, Yakshongas and 114 guests