Page 1 of 1
Count cells in Excel column containing values
Posted: 08 Dec 2018, 22:46
by 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.
Re: Count cells in Excel column containing values
Posted: 09 Dec 2018, 23:22
by 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
}
Re: Count cells in Excel column containing values
Posted: 10 Dec 2018, 17:31
by hidefguy
Tried this one too, but got skunked
CountUsed := FileFrom.WorksheetFunction.CountA(Sheets(1).Range("M:M"))
loop, % CountUsed
{
send, ^{PgUp}
sleep, 1000
}
Re: Count cells in Excel column containing values
Posted: 10 Dec 2018, 17:41
by 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
}
Re: Count cells in Excel column containing values
Posted: 11 Dec 2018, 10:23
by awel20
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 := ""
Re: Count cells in Excel column containing values
Posted: 11 Dec 2018, 10:41
by hidefguy
The first one worked. TY very much awel20
Re: Count cells in Excel column containing values
Posted: 11 Dec 2018, 10:44
by 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.
Re: Count cells in Excel column containing values
Posted: 11 Dec 2018, 10:54
by 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
Re: Count cells in Excel column containing values
Posted: 11 Dec 2018, 11:48
by 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)
Re: Count cells in Excel column containing values
Posted: 12 Dec 2018, 00:43
by hidefguy
First one worked, first try. Thanks again friend
