Excel UDF RangeRead with a Filter Set

[HighlanderSword 1]

Hello,

 

I have an Excel Document that has 147 columns, in Column 7 I have a filter set to limit the amount of records returned.

I need to read the results from the filtered set and only return 3 columns , as:au with only the filtered results being returned

How do you do this using rangeRead or another function ?

 

Edited August 11, 2015 by HighlanderSword

[water 2,069]

What have you tried so far and what were the results?

[HighlanderSword 1]

 

Below are the ones I've been trying

 

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.SpecialCells($xlCellTypeVisible), 2)

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.SpecialCells(12).columns("a:c"), 2)

 Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:Ax"), 2)

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.sheet("Data").columns("a1:c5"), 2)

[water 2,069]

At the moment _Excel_RangeRead stops at the first hidden line and only returns those records read so far.

 

 

[water 2,069]

As a workaround.
You could read the needed columns and then filter out the hidden rows:

#include <Excel.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
Global $aRange[100][2], $iRows = 0
For $oRow In $oWorkbook.ActiveSheet.AutoFilter.Range.Rows ; Loop through the rows of the filtered range
    If $oRow.Hidden = False Then ; If row is visible process it
        $aRange[$iRows][0] = $oRow.Columns(1).Value ; Move value, formula of column 1 to the array
        $aRange[$iRows][1] = $oRow.Columns(2).Value ; Move value, formula of column 2 to the array
        $iRows = $iRows + 1
    EndIf
Next
ReDim $aRange[$iRows][2]
_ArrayDisplay($aRange)

 

[HighlanderSword 1]

ok,I will remove the filter and do a standard rangeread and the filter out from the array what I need to get

[water 2,069]

No need to remove the filter.
My example grabs all rows from the filtered range and only grabs those rows which are not hidden.

[HighlanderSword 1]

thanks , that worked !!!