Jump to content
Sign in to follow this  
nooneclose

[SOLVED] How to search through a column and delete certain names.

Recommended Posts

My program has to first search for names in Column D that do not match up with column C. I got that search to work using arrays. It was slow and I could not figure out how to delete them so I just manually put coded the names that do not belong. I found their cell location but I do not know how to store that location and delete it.

This is what I have so far.

Local $NameToDelete1[6]  = _Excel_RangeFind($OpenWorkbook, "Smith, Bill")
_ArrayDisplay($NameToDelete1, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")
_Excel_RangeDelete($OpenWorkbook.ActiveSheet, $NameToDelete1[2], $xlShiftUp)

 

Please help, I wanted to have this program done yesterday but I did not see this problem until yesterday. 

Edited by nooneclose

Share this post


Link to post
Share on other sites

Just to make sure I understand your problem:

You have an Excel Workbook where you want to delete all rows where the value in column D doe not match the value in column C of the same row?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-06-17 - Version 1.5.0.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
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

@water I was actually able to figure this one out on my own. But if you have a better way please share. 

As of now, this is what I use:

Local $NameToDelete1  = _Excel_RangeFind($OpenWorkbook, "Smith, Bob")
Local $Name           = $NameToDelete1[0][3]
Local $NameCell       = $NameToDelete1[0][2]
Local $CellNumber     = StringSplit($NameCell, "A, B, C, D, E")
Local $CellRange      = $NameCell & ":E" & $CellNumber[2]
_Excel_RangeDelete($OpenWorkbook.ActiveSheet, $CellRange, $xlShiftUp)

I have to manually make the code for every name, (that I know of) that should not be on the roster. I would greatly appreciate it if the program would search for me for any name in column D that is not found in column C and then delete that cell (upwards) which holds the name in column D and their time in column E. 

Edited by nooneclose

Share this post


Link to post
Share on other sites

@IAMK Yes, it has to be done in AutoIt. That is the whole point of creating an automation script, to make the program do something that I do not want to.  Like I said earlier though I have already figured out how to accomplish this task plus I have refined the process since then. 

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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By shelly
      I have to press tab to reach a selected button then double click on that
      Here's my script but its not working:
      WinActivate("Policy Decisions -- Webpage Dialog","")
      Sleep(3000)
      Send("{TAB}")
      Send("{TAB}")
      Send("{TAB}")                    // at this point its working according to my need  but double click is not working
      Mouseclick("Left",510, 320, 2, 0)

    • By TheDcoder
      See this thread for info:
    • By nacerbaaziz
      goodmorning autoit team
      today am comming with some winhttp problems, i hope that you can help me to solve them.
      the first problem
      is when opening a request
      my forums api allow me to delete any post using the api key
      all functions work, i mean post / get
      but when i tried to use the delete verb it's gave me an html 404 error
      here is what am tried
      #include "WinHttp.au3" ; Open needed handles Global $hOpen = _WinHttpOpen() Global $hConnect = _WinHttpConnect($hOpen, "xxxxxxxx.com") ; Specify the reguest: Global $hRequest = _WinHttpOpenRequest($hConnect, "Delete", "/vb/Api/posts/10447/?hard_delete=true", default, default) _WinHttpAddRequestHeaders($hRequest, "XF-Api-Key:xxxxx") _WinHttpAddRequestHeaders($hRequest, "XF-Api-User:xxxxx") ; Send request _WinHttpSendRequest($hRequest) ; Wait for the response _WinHttpReceiveResponse($hRequest) Global $sHeader = 0, $sReturned = 0 ; If there is data available... If _WinHttpQueryDataAvailable($hRequest) Then $sHeader = _WinHttpQueryHeaders($hRequest, $WINHTTP_QUERY_CONTENT_DISPOSITION) ;Or maybe: ; $sHeader = _WinHttpQueryHeaders($hRequest, BitOR($WINHTTP_QUERY_RAW_HEADERS_CRLF, $WINHTTP_QUERY_CUSTOM), "Content-Disposition") Do $sReturned &= _WinHttpReadData($hRequest) Until @error msgBox(64, "", $sReturned) endIf ; Close handles _WinHttpCloseHandle($hRequest) _WinHttpCloseHandle($hConnect) _WinHttpCloseHandle($hOpen)  
      and here is the error message
      <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN"> <html><head> <title>403 Forbidden</title> </head><body> <h1>Forbidden</h1> <p>You don't have permission to access /vb/Api/posts/10447/ on this server.<br /> </p> </body></html>  
      i hope you can help me 
      thanks in advance
    • By nooneclose
      Why can I not click on either of my input fields?
      #Region ### START Koda GUI section ### Form= Global $Form1 = GUICreate("Form1", 730, 437, 192, 124) ;GUISetFont(14, 800, 0, "MS Sans Serif") GUISetBkColor(0xC0DCC0) Global $Label1 = GUICtrlCreateLabel("Enter your Password. UserName is auto filled", 62, 40, 604, 41, $SS_CENTER) GUICtrlSetFont(-1, 26, 800, 0, "MS Sans Serif") Global $Label2 = GUICtrlCreateLabel("User Name:", 120, 130, 604, 31, $SS_LEFT) GUICtrlSetFont(-1, 20, 800, 0, "MS Sans Serif") Global $Label3 = GUICtrlCreateLabel("Password:", 135, 210, 604, 31, $SS_LEFT) GUICtrlSetFont(-1, 20, 800, 0, "MS Sans Serif") Global $Input1 = GUICtrlCreateInput(@UserName, 274, 130, 185, 32) GUICtrlSetFont(-1, 14, 800, 1, "MS Sans Serif") Global $Input2 = GUICtrlCreateInput("Password", 274, 210, 185, 32, $ES_PASSWORD) GUICtrlSetFont(-1, 14, 800, 0, "MS Sans Serif") Global $OK = GUICtrlCreateButton("OK", 274, 270, 185, 57) GUICtrlSetFont(-1, 20, 800, 0, "MS Sans Serif") GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $OK Global $UserName = GUICtrlRead($Input1) Global $PassWord = GUICtrlRead($Input2) ExitLoop EndSwitch WEnd ; Delete the GUI window GUIDelete()  
    • By cruisepandey
      Hi, 
      I have a string like this : 
      Global $Msga = "urrent directory is /send.  (Submission of file with log number 29381077284 is confirmed)";
      I want to extract the number 29381077284  from the string. I did StringSplit to split based on "(" and then use space to reach there, But it's not a good choice. 
      Can anyone help me with regular expression to find the number from String using AutoIT. TIA
×
×
  • Create New...