delete rows from array if just 1 empty cell exist

[kemo1987]

Hello
i need some help to remove some rows from the array before i insert it to new excel sheet 
after i did search all i found was deleting entire empty rows but in my case i want to delete rows only if the "QTY" cell is empty 

Thanks in advance
 

expandcollapse popup

Func _movetosheet()
    Local $oExcel =_Excel_Open()
    sleep(1000)
    $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\agouzaold.xls")
    sleep(1000)
    $datawb.worksheets("agouzaold").select
    sleep(1000)
    $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
    sleep(1000)
    $mydata = _Excel_RangeRead($datawb, Default, Default )
    sleep(1000)
    _ArrayColDelete($mydata, 0)
    _ArrayColDelete($mydata, 0)
    _ArrayColDelete($mydata, 1)
    _ArrayColDelete($mydata, 1)
    _ArrayColDelete($mydata, 1)
    _ArrayColDelete($mydata, 2)
    _ArrayColDelete($mydata, 3)
    _ArrayColDelete($mydata, 3)
    _ArrayColDelete($mydata, 3)
    _ArrayColDelete($mydata, 3)
    _ArrayColDelete($mydata, 3)
    _ArrayColDelete($mydata, 4)
    _ArrayColDelete($mydata, 4)
    sleep(1000)
    _ArraySwap($mydata, 0, 2, True)
    _ArraySwap($mydata, 0, 1, True)
    _ArraySwap($mydata, 0, 3, True)
    sleep(1000)
    _ArrayDelete($mydata, 0)
    _ArrayDelete($mydata, 0)
    _ArrayDelete($mydata, 0)
    _ArrayDelete($mydata, 0)
    _ArrayDelete($mydata, 0)
    _ArrayDelete($mydata, 0)
    _ArrayDelete($mydata, 0)
    _ArrayDelete($mydata, 0)
    _ArrayDelete($mydata, 0)
    sleep(1000)
    _ArrayDelete($mydata, UBound($mydata) - 1)
    _ArrayDelete($mydata, UBound($mydata) - 1)
    sleep(1000)
    ;If IsArray($mydata) Then _ArrayDisplay($mydata)
    _Excel_BookClose($datawb)
    sleep(1000)
    ;--------------------------------
    Global $pExcel = _Excel_Open()
    sleep(1000)
    Global $oWorkbook = _Excel_BookNew($pExcel, 1)
    sleep(1000)
    Global $sWorkbook = @ScriptDir & "\agouza.xlsx"
    sleep(1000)
    _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True)
    sleep(1000)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $mydata, "a1", Default, True)
    sleep(1000)
    _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True)
    Sleep(3000)
    _Excel_BookClose($oWorkbook)
    Sleep(3000)
    $oExcel.Quit()
    sleep(2000)
EndFunc

 

Edited May 16, 2023 by kemo1987

[Nine]

Try this :

#include <Excel.au3>

_movetosheet()

Func _movetosheet()
    Local $oExcel =_Excel_Open()
    $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\Test.xls")
    $datawb.worksheets(1).select
    $mydata = _Excel_RangeRead($datawb, Default, Default )
    For $i = UBound($mydata) - 1 To 1 Step -1
      If Number($mydata[$i][3]) = 0 Then _ArrayDelete($mydata, $i)
    Next
    _ArrayDisplay($mydata)
    _Excel_BookClose($datawb)
    Local $oWorkbook = _Excel_BookNew($oExcel, 1)
    Local $sWorkbook = @ScriptDir & "\TestOut.xls"
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $mydata, "a1", Default, True)
    _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookNormal, True)
    _Excel_BookClose($oWorkbook)
    _Excel_Close($oExcel)
EndFunc

ps. no need to put sleep everywhere

[kemo1987]

1 hour ago, Nine said:

Try this :

#include <Excel.au3>

_movetosheet()

Func _movetosheet()
    Local $oExcel =_Excel_Open()
    $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\Test.xls")
    $datawb.worksheets(1).select
    $mydata = _Excel_RangeRead($datawb, Default, Default )
    For $i = UBound($mydata) - 1 To 1 Step -1
      If Number($mydata[$i][3]) = 0 Then _ArrayDelete($mydata, $i)
    Next
    _ArrayDisplay($mydata)
    _Excel_BookClose($datawb)
    Local $oWorkbook = _Excel_BookNew($oExcel, 1)
    Local $sWorkbook = @ScriptDir & "\TestOut.xls"
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $mydata, "a1", Default, True)
    _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookNormal, True)
    _Excel_BookClose($oWorkbook)
    _Excel_Close($oExcel)
EndFunc

ps. no need to put sleep everywhere

Thanks for quick reply and the solution it works perfect
 

[pixelsearch]

imho _ArrayDelete() should be called only once, outside the For...Next loop, using a string as 2nd parameter, with multiple items to delete separated by a semi-colon (;)
It should be faster in case the original array is a big one, with plenty of rows to delete.