Jump to content
Taxyo

Delete Excel Row based on value in Column of that Row

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! 

Share this post


Link to post
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 2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-03-21 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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. 

Share this post


Link to post
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? 

Share this post


Link to post
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 2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-03-21 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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

  • Similar Content

    • By Zaoka
      Hi guys,
      i have simple report in PowerPivot that shows Orders (Values) by Regions (Row) and Weeks (Columns). In Filter field is WeekDAYS (Monday,Tuesday,Wednesday,Thursday etc )
      how to filter WeekDAYS Filed on WEEKDAYYesterday with autoit ?
      my junky try
      #include <Date.au3> #include <Excel.au3> Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1) Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb") Sleep (5000) $oExcel.ActiveWorkbook.RefreshAll Sleep (5000) $oExcel.Application.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)  
      Error result
      $oExcel.Application.Sheets("PivotTable1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday) $oExcel.Application^ ERROR  
    • By rdaneelol
      I have a strange behavior in an Autoit program.  
      The program works perfectly in the original environment I created the program for - for Windows 7 and Office  2010.
      My workplace is migrating to Windows 10 with Office 2016.  When I run this program in that new environment,  the code actually executes without any errors, however, the excel process which was opened to read a spreadsheet/workbook does not close while the program is running.  If you exit the program, the excel process ends at that point...
      I could ignore this behavior - one little excel process hanging out there is not going to kill anything, however - it just bugs me !
      While troubleshooting the issue, I placed a number of error checks after the excel close - and the close actually reports that it is successful. 
      I tried a few varieties of closing the excel process, and none of the methods tried seemed to actually work as well at the one in my code - the only downside being that it has to wait until the program finishes to actually close.
      Any ideas on why an excel close would hang until program exit ?
       
      Local $oExcel = _Excel_Open(False, False, False, False, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $sUserRoleMenuXLS, False, true ) If @error Then     MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening workbook '" & $sUserRoleMenuXLS & @CRLF & "@error = " & @error & ", @extended = " & @extended)     _Excel_Close($oExcel, False, False)  Exit EndIf ;  this section will find the user id in the first 3 columns of the user/menu spreadsheet, and if it finds it - returns the value stored in the cell 2 locations to the right... With $oExcel.ActiveSheet.Range("A:C").Find (@UserName)     $Match = .Find (@UserName)     If (Not IsObj($Match)) or (stringlen($Match.Offset(0, 2).value) = 0)    Then         MsgBox($MB_SYSTEMMODAL, 'UIPLauncher Error', 'No Menu assigned for user - Contact Support.') ; Display a warning if the script isn't compiled or the file doesn't exist.         ;_Excel_BookClose($oWorkbook, False)         _Excel_Close($oExcel, false, false)         Exit     Else         Local  $cResult = $Match.Offset(0, 2).value     EndIf EndWith $oWorkbook.Saved = True _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel, false, false) ;close does not seem to work on windows 10 and office 2016  
    • By VinMe
      $aTdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $LastRow) $aTDSLOC = _Excel_RangeRead($oWorkbook1, Default, "H1:H" & $LastRow) MsgBox(0, "TAKSY OUTPUT LAST ROW", $LastRow) _ArrayTrim($aTDSLOC, 1, 1) _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, $aTDSLOC, "H2") $oWorkbook1.Sheets("DS_List").Activate $mLastRow = $oWorkbook1.ActiveSheet.UsedRange.Rows.Count $aMdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $mLastRow) MsgBox(0, "MASTER OUTPUT LAST ROW", $mLastRow) _Excel_RangeReplace($oWorkbook1, Default, "F2:F" & $mLastRow, "N/A", "n/a") ;check the requiremetn $aMLOC = _Excel_RangeRead($oWorkbook1, Default, "F1:F" & $mLastRow) ;LOC NUMBER NEEDS TO BE REPLACED WITH.. _ArrayDisplay($aTDSLOC) _ArrayDisplay($aMLOC) _ArrayDisplay($aMdslist) ;DATA COMPARISION BETWEEN TAKSY LIST WITH LOC FROM SAP TO MASTER LIST WITH LOC. $oWorkbook1.Sheets("OUTPUT").Activate For $i = 1 To $LastRow - 1 For $j = 1 To $mLastRow - 1 If $aTdslist[$i] == $aMdslist[$j] And $aTDSLOC[$i] <> $aMLOC[$j] Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 3 EndIf If $aTdslist[$i] == $aMdslist[$j] And $aMLOC[$j] == "n/a" And _Excel_RangeRead($oWorkbook1, Default, "I" & ($i + 2)) == "NO KIT" Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 2 _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, "*", "H" & ($i + 2)) EndIf Next Next MsgBox($MB_SYSTEMMODAL, "Status", "Loc extraction, comparision and consolidation is Done", 2) DEAR TEAM, I am facing issue w.r.t subscript errors kindly help me in solving...
      456.xlsx
    • By VinMe
      $oWorkbook = _Excel_BookAttach($sWorkbook) $oWorkbook.sheets("Sheet1").activate Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "") _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, $sMSN) can anyone help me here, excel filer is not working with the above code.. attached excel
      input: 12
      Thank you in advance!!
      to check.xlsx
    • By Rskm
      Hi, I am using excel as input media for my program. The excel file (i tried with .xls, .xlsx and .xlsm format) has inputs which the autoit script reads during the run and performs few calculations. Some times (not always), after the run, when i try to open the excel file manually, the file doesnt open at all in excel. see the screenshot attached. However, if the execute the autoit script, the scripts still reads the existing data from that excel and performs the calcs. I copied the excel file to another computer and there too, it doesnt open.  So, after this, i cannot edit the excel forever (if i need to change any inputs). It is only this particular file that got affected. other excel files works normal.  What could be the problem here.  please help as this is a new challenge for me during my program development. 

×
×
  • Create New...