ahk: Excel Lastrow

[RinkaDink]

Good Evening. have a curiosity when using this code to Loop in Excel to last row. My test Excel file has 3 rows but ahk is Looping until row 5I am new to ahk and using VBA in the past
anyone know why lastrow is not correct ?

Code: Select all

Xl := ComObjActive("Excel.Application")
Xl.Visible := True
xlUp := -4162

For Sheet in Xl.Worksheets
   
        Loop, % Sheet.Cells(Sheet.Rows.Count, 1).End(xlup).Row
        {
Var1 := Sheet.Cells(A_Index, 1).Value

if (Var1 ~= "^\d.{3}\d{6}$")
	MsgBox pass
else
	MsgBox fail
 }    
return

Best regards
Henry

[Datapoint]

Good Evening. have a curiosity when using this code to Loop in Excel to last row. My test Excel file has 3 rows but ahk is Looping until row 5I am new to ahk and using VBA in the past
anyone know why lastrow is not correct ?

Code: Select all

Xl := ComObjActive("Excel.Application")
Xl.Visible := True
xlUp := -4162

For Sheet in Xl.Worksheets
   
        Loop, % Sheet.Cells(Sheet.Rows.Count, 1).End(xlup).Row
        {
Var1 := Sheet.Cells(A_Index, 1).Value

if (Var1 ~= "^\d.{3}\d{6}$")
	MsgBox pass
else
	MsgBox fail
 }    
return

Best regards
Henry

Code: Select all

Xl := ComObjActive("Excel.Application")
Xl.Visible := True
xlUp := -4162

For Sheet in Xl.Worksheets
	For Cell in Sheet.Range(Sheet.Cells(1, 1), Sheet.Cells(Sheet.Rows.Count, 1).End(xlup))
	{
		Var1 := Cell.Value
		if (Var1 ~= "^\d.{3}\d{6}$")
			MsgBox pass
		else
			MsgBox fail
	}
Xl := ""
return

[RinkaDink]

Good Morning.
thanks @ Datapoint.

There is still more Messages appearing then existing rows. This is really strange.
It seems that the code runs through all sheets.

How can I tell ahk just to do it in sheet(1) ? in VBA would be Sheets(1) but doesnt work.

Best regards
Henry

[RinkaDink]

ok, got it ^^

sheet := WB.Sheets(1)

[FanaticGuru]

Since you are looping anyways there is no real need to know ahead of time how many rows there are. You can just start looping through the cells with a check to stop when you hit a blank cell. This makes the code somewhat simpler.

Code: Select all

xlApp := ComObjActive("Excel.Application")

For Cell in xlApp.Sheets(1).Columns(1).Cells
	if (Cell.Value = "")
		return
	else if (Cell.Value ~= "^\d.{3}\d{6}$")
		MsgBox pass
	else
		MsgBox fail

FG