Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Creating array of visible (filtered) rows in excel
Hi Gintaras (and others),
I have a very large excel sheet (~30k rows), and I want to run a process on only the rows that are visible after a filter is applied. Ideally, there would be some way to have a single dimensional array of visible rows (or can do a <sel> range if I select after doing the filter. Then I could do something like

Copy      Help
ARRAY(int) FilteredRows
[some function to fill array with row numbers of visible/filtered cells]
for _i 0 FilteredRows.len
,if !empty(es.Cell(1 FilteredRows[i])) 
,,es.SetCell(2 FilteredRows[i] "not empty"

I saw something like this 
Copy      Help
Excel.Range r=es._Range("<sel>")

which would work I think if I select the visible rows after filter applied, but now sure how to use the Range object like an array.

Thanks for any thoughts!,
Copy      Help
typelib Excel
Excel.Application a._create
Excel.Workbook b=a.Workbooks.Add()
es.Activate(4) ;;activate Excel = "Plan de Trabajo y Retorno del C"
int w=win("" "XLMAIN")
max w
str f.expandpath("$desktop$\test45.xls")
del f; err
b.SaveAs(f Excel.xlNormal @ @ @ @ 1)

 Data Dummy and filter"A1:C5").Value = "XXX YYY ZZZ""A3").Value = "Row to filter"
 Apply Filter"$A$1:$C$5").AutoFilter(1,"XXX YYY ZZZ")
 Select Dummy"A1:C5").Select
 Select visible cells

Forum Jump:

Users browsing this thread: 1 Guest(s)