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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-13 - Version 0.5.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
 
Tutorials:

ADO - 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  

  • Similar Content

    • By _leo_
      Hey there! 😃
      I am having a problem with the _Excel_RangeFind. I am trying to search for a value in a particular cell range. The script copies the value from the internet. Copying and saving as a variable is working fine, but as soon as it should find the value in excel, nothing happens. ( I am not getting an error)
      #include <Excel.au3> Func Excel() Send("{CTRLDOWN}") Send("{c}") Send("{CTRLUP}") Local $sName = ClipGet() ;Text Local $sShortName = StringTrimRight ( $sName, 1) ;delete one letter Local $bOpenWorkBook = False, $oExcel = _Excel_Open() Local $sFilePath = "C:\Users\Acer\OneDrive\xyz.xlsx" Local $oWorkbook $oWorkbook = _Excel_BookAttach($sFilePath) If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath) $bOpenWorkBook = True EndIf sleep(15000) Send("{LWINDown}") Send ("{up}") ;maximize window Send("{LWINup}") sleep(1000) _Excel_RangeFind ($oWorkbook, $sShortName, "A3:A56") EndFunc Is anyone familiar with this problem or am I just missing some basic stuff? 
      Thanks for help!
    • By _leo_
      Hey guys
      I'm new to autoit, so this could be a simple question. I'm trying to read the value from the currently selected cell in Excel. I read on the forum and tired to find videos, but I couldn't quite get to it. 
      This is what I have got so far:
       
      Local $oExcel_1 = _Excel_Open()
      Local $var = "C:\Users\Acer\xy"
      Local $oWorkbook = _Excel_BookOpen($oExcel_1,$var)
       
      Local $_read1 = _Excel_RangeRead($oWorkbook, Default.Application.ActiveCell.Address)
       
      Whatever I try, I either get an error or it only reads "0".
       
      Thank you very much for any helpful thoughts!
    • By 2Toes
      Hey there,
      I have a program that I'm working on, and I have a button that's supposed to stop a running function, when it's pressed.
      However, I cannot get that to work.
      I put together a sample script that is set up in the same way:
      #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #Region ### START Koda GUI section ### Form= Opt("GUIOnEventMode", 1) Global $Form1 = GUICreate("Form1", 290, 106, 192, 124) GUISetOnEvent($GUI_EVENT_CLOSE, "_Exit") Global $btnStart = GUICtrlCreateButton("Start", 24, 24, 99, 49) GUICtrlSetOnEvent($btnStart, "_Start") Global $btnStop = GUICtrlCreateButton("Stop", 152, 24, 99, 49) GUICtrlSetOnEvent($btnStop, "_Stop") GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### While 1 ;~ $nMsg = GUIGetMsg() ;~ Switch $nMsg ;~ Case $GUI_EVENT_CLOSE ;~ Exit ;~ EndSwitch WEnd Func _Start() Global $bStartFlag = True $num = 1 While $bStartFlag ConsoleWrite($num & @CRLF) Sleep(1000) $num += 1 WEnd ConsoleWrite("Exited loop.." & @CRLF) EndFunc Func _Stop() Global $bStartFlag = False EndFunc Func _Exit() Exit EndFunc  
      With the code above, while the _Start() func is running, the Stop button doesn't do anything.
      I even threw a MsgBox into the _Stop() func, to see if the script was at least accessing the _Stop() func. But that showed that the _Stop() func is not being accessed at all while the _Start() func is running.
      I'm sure this is a simple solution that I'm just over looking. But I can't figure out what that solution is lol.
      Any help here would be greatly appreciated!
      Thank you in advance!  
    • By jantograaf
      Hi all,
      I'm trying to write a script that connects with a VBA/COM API to get the status of a connected phone. I've been looking up and down this forum for tips or other user's experiences, but I can't seem to find anything (even remotely) similar. It shouldn't be so hard to do, however.
      Software I'm trying to connect to
      I'm trying to integrate CallCenter by using their API, which is documented over here : JustRemotePhone API Reference
      Things I've tried
      I've tried using ObjCreate but I don't get any result, it always returns the same (negative) error.
      #Version 1 tried ObjCreate("JustRemotePhone.RemotePhoneService") #Version 2 tried ObjCreate("JustRemotePhoneCOM.RemotePhoneService") #Version 3 tried ObjCreate("JustRemotePhoneCOM.RemotePhoneService.Application") None of the three versions I tried seem to deliver any result other than a negative error value which basically says that the given class is not valid.
      I am starting to get the hang of AutoIt by now, but unmanaged programming languages and object-oriented stuff is still quite a grey zone for me. If anyone could help me 'talk' to this application, I'd be immensely grateful!
      Thanks in advance and kind regards from Belgium!
       
      Jan
×
×
  • Create New...