$xlCellTypeVisible - (Moved)

[VinMe]

Dear all, 

I am unable to get the right result after applying the filter to the excel. please let me know on the same.

issue: After applying the filter the output $lastRow11 not giving the right output of complete visible rows. (its breaking at row skips)

 

;DATA EXTRACTION FROM LOC EXCEL
;=============================================================================
$oWorkbook = _Excel_BookAttach($sWorkbook)
Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
;~ Local $LastRow1 = ($oWorkbook.ACTIVESHEET.Range("A1").SpecialCells($xlCellTypeLastCell).Row)
$LastRow1 = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
MsgBox(0, "lastrow1", $LastRow1)

_Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")

Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells($xlCellTypeVisible)
Local $LastRow11 = $oLocDS.rows.count    ;error output
MsgBox(0, "lastrow11", $LastRow11)
Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells($xlCellTypeVisible)
Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)

_ArrayDisplay($aLocDS1)
_ArrayDisplay($aLocNr1)
_ArrayTrim($aLocDS1, 6, 1)
_ArrayTrim($aLocNr1, 6, 1)
_ArrayTrim($aLocNr1, 6, 0)
_ArrayDisplay($aLocDS1)
_ArrayDisplay($aLocNr1)

[Melba23]

Moved to the appropriate forum.

Moderation Team

[Nine]

Try this, works for me with AutoIt example :

Local $LastRow = $oExcel.ActiveSheet.AutoFilter.Range.Rows.SpecialCells($xlCellTypeVisible).Count-1

Use it after the filter has been applied...

ps. when you post code, use this tool.

Edited September 19, 2019 by Nine

[VinMe]

thank you nine for the reply, i have tried with that but the issue still exist with the below part of code.

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <StringConstants.au3>

Local $sWorkbook = FileOpenDialog("Choose file loc Extract file", @ScriptDir, "ALL Excel Files (*.xlsx), *.xls")
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
;~ $oWorkbook = _Excel_BookAttach($sWorkbook)
Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
_Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")

Local $LastRow1= $oExcel.ActiveSheet.AutoFilter.Range.Rows.SpecialCells($xlCellTypeVisible).Count-1
MsgBox(0, "lastrow1", $LastRow1)

Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells(12) ;not getting complete filtered list
Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells(12) ;not getting complete filtered list

Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)

_ArrayDisplay($aLocDS1)
_ArrayDisplay($aLocNr1)

Attached input excel for your reference.

Result  which i need is set of filtered array set in variable $aLocDS1 and $aLocNr1

thank you in advance!!

BR

vin

vLOC_.XLSX

Edited September 20, 2019 by VinMe

[Nine]

Ah I see.  Try this then :

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <StringConstants.au3>

Local $sWorkbook = FileOpenDialog("Choose file loc Extract file", @ScriptDir, "ALL Excel Files (*.xlsx), *.xls")
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
;~ $oWorkbook = _Excel_BookAttach($sWorkbook)
Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")

_Excel_FilterSet($oWorkbook, $oExcel.ActiveSheet, "AF1", 32, "*" & $sMSN & "*")
Local $oFilter= $oExcel.ActiveSheet.AutoFilter.Range.Rows.SpecialCells($xlCellTypeVisible).copy

Local $oNewSheet = _Excel_SheetAdd ($oWorkbook, Default, False, 1, "Temp")
$oNewSheet.paste
$oNewSheet.Range("A1").Select

Local $LastRow = $oWorkbook.ActiveSheet.Usedrange.Rows.Count
Local $aLocDS = _Excel_RangeRead($oWorkbook, Default, "S1:S" & $LastRow)
Local $aLocNr = _Excel_RangeRead($oWorkbook, Default, "A1:A" & $LastRow)

_Excel_SheetDelete ($oWorkbook)

_ArrayDisplay($aLocDS)
_ArrayDisplay($aLocNr)

 

[VinMe]

oh Nine thank you! i did used this logic  anyways your code is better