Count cells in Excel column containing values

[hidefguy]

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]

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]

Tried this one too, but got skunked

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

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

[hidefguy]

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]

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]

The first one worked. TY very much awel20

[awel20]

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]

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]

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]

First one worked, first try. Thanks again friend