Excel COM - Visible_rows()

Post your working scripts, libraries and tools
Gicu
Posts: 111
Joined: 19 Aug 2014, 08:19
Location: Italy

Excel COM - Visible_rows()

06 Jul 2015, 07:22

My first topic here... :oops:

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
Attachments
Test.zip
(6.8 KiB) Downloaded 86 times
Last edited by Gicu on 15 Jul 2015, 03:43, edited 2 times in total.
Guest10
Posts: 578
Joined: 01 Oct 2013, 02:50

Re: Excel COM - Visible_rows()

14 Jul 2015, 16:37

Thanks, could you provide a functioning example? :oops: :morebeard:
Gicu
Posts: 111
Joined: 19 Aug 2014, 08:19
Location: Italy

Re: Excel COM - Visible_rows()

15 Jul 2015, 03:48

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

Return to “Scripts and Functions”

Who is online

Users browsing this forum: EvetsNalon and 24 guests