Jump to content

Delete Excel Row based on value in Column of that Row


Taxyo
 Share

Recommended Posts

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! 

Link to comment
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...