Delete Excel Row based on value in Column of that Row

[Taxyo]

Hi,

 

I've been trying to automate modification of an excel file and the last thing I am stuck on is deleting all the rows where the value of Column 13 is 0. 

I believe the error is due to me not fully understanding the syntax so this is where I'm stuck: 

 

Func Hotkey2()

   Global $aUsedRange = _Excel_RangeRead($oWorkbook, 1)
   _ArrayDisplay($aUsedRange)
   For $iRow = UBound($aUsedRange) - 1 to 3 Step -1
      If $aUsedRange[$iRow][13] = 0 Then
         _Excel_RangeDelete($oWorkbook.Worksheets(1), $aUsedRange[$iRow] & ":" & $aUsedRange[$iRow], default, 1)
         If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      EndIf
   Next

EndFunc

 

While my script properly locates the row which contains value 0 in Column 13, I am not sure how to set it to the corresponding row in the excel workbook?  My above experiment gives me $vRange error and I've been toying around with it to no avail. The only way I get the Script to delete a row is by actually specifying "4:4" or "6:8" etc. 

Where am I going wrong?

 

Thanks! 

[water]

The index of an array starts with 0, the row in Excel start with 1. So you need to to add 1 to get the row in Excel.
What you do is to process the content of the array where you need to process the index of the array:

Func Hotkey2()
   Local $aUsedRange = _Excel_RangeRead($oWorkbook, 1)
   _ArrayDisplay($aUsedRange)
   For $iRow = UBound($aUsedRange) - 1 to 3 Step -1
      If $aUsedRange[$iRow][13] = 0 Then
         _Excel_RangeDelete($oWorkbook.Worksheets(1), $iRow+1 & ":" & $iRow+1, Default, 1)
         If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      EndIf
   Next
EndFunc

 

Edited November 11, 2019 by water

[Taxyo]

Ah,

 

That makes total sense. I was too hung up on the part of removing 1 from the UBound value without realising that of course $iRow required the opposite.

Thanks - as usual,  gonna have to test this at work tomorrow but it makes sense. 

[junkew]

you could speed things up by deleting a range at once 

see https://stackoverflow.com/questions/17194394/vba-how-to-delete-filtered-rows-in-excel

[Taxyo]

Hmm interesting, how would I implement that to Autoit? 

I tried testing it by first manually sorting so all the columns with value 0 in "N:N" was visible, and then running the following: 

Func Hotkey3()
   $oWorkbook.ActiveSheet.Range("N1:N999") & lines).SpecialCells _
    (xlCellTypeVisible).EntireRow.Delete
 EndFunc

 

But I am not the best when it comes to VBA (not the best = done it twice) , and my programming journey started about 2 weeks ago so I am still learning as I go. 

 

I get the error: 

".../ (64) : ==> Unknown function name.:
$oWorkbook.ActiveSheet.Range("N1:N999") & lines).SpecialCells (xlCellTypeVisible).EntireRow.Delete
$oWorkbook.ActiveSheet.Range("N1:N999") & ^ ERROR

 

So I assume the code has to be slightly modified as to what I got from Stackoverflow? 

[water]

If you want to delete all visible rows I suggest you have a look at the wiki. Replace RangeRead with RangeDelete