Excel COM - Visible_rows()

[Gicu]

My first topic here...

Code: Select all

;•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••
; Function				Visible_rows
; Description			returns an object of visible rows
;
; xl					Active excel com object
; e						Excluded rows

; Example:
; Open and filter (or not) an excel workbook
xlr := ComObjActive("Excel.Application")
r := Visible_rows(xlr, "1|2|3")	
Loop % r.maxindex()
	MsgBox % xlr.Range("A" r[A_Index]).value

Visible_rows(xl := "", e := ""){

	b := A_BatchLines
	SetBatchLines, -1
	if !IsObject(xl)
		xl := ComObjActive("Excel.Application")
	
	r := []
	r.Areas_Count := xl.ActiveSheet.usedrange.specialCells(12).Areas.Count
	loop, % r.Areas_Count
	{
		i := strsplit(RegExReplace(xl.ActiveSheet.usedrange.specialCells(12).Areas(A_Index).Address[0,0], "[a-zA-z]+"), ":")
		i.2 := i.2 ? i.2 : i.1
		r.Push(i.1)
		if (i.1 == i.2)
			Continue
		start := i.1
		Loop
		{
			start += 1
			r.Push(start)
		} until (start = i.2)
	}
	o_e := StrSplit(e, "|")
	loop % r.maxindex()
	{
		nr := A_Index
		loop % o_e.maxindex()
			if (r[nr] = o_e[A_Index])
				r.RemoveAt(nr)
	}
	SetBatchLines, % b
	Return r
}

edit: now it should work with a single column
edit2: added test.zip

[Guest10]

Thanks, could you provide a functioning example?

[Gicu]

download test.zip and (try to ) run test.ahk