Jump to content

How to select or delete a column in excel sheet on the basis of heading in the first row

Recommended Posts


I wrote a script which deletes a single or range of columns from an excel file, however, I found that the file is frequently modified and the automation is not smooth.

The task can be achieved in two ways, please help me in whichever is simple.

1) Instead of column number, I delete single column or range of columns on the basis of heading in the first cell of the column. This way I will be able to select the desired columns even if the column number changes.

2) I select the columns on the basis of heading in the first cell of each column and paste it in a new sheet or new excel file. This way I will get the desired column where ever they are in the file.



Share this post

Link to post
Share on other sites

The Excel UDF that comes with AutoIt should provide the necessary functions: _Excel_RangeRead and _Excel_RangeDelete.

My UDFs and Tutorials:


Active Directory (NEW 2017-04-18 - Version - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version - Download - General Help & Support

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

    • rudi
      By rudi
      I used the various "_Excel_*()" funktions to open workbooks, read and write cells.
      I was looking through the helpfile for the _EXCEL_* funktions, if there is a direkt way to set background color or text color -- if there is one, then I missed it?
      Searching the forum I found this posting demonstrating how to do this task using ...
      Is there a way to set the background color etc. with the native "_EXCEL_*" as well?
      Regards, Rudi.
    • Theodoor
      By Theodoor
      I am trying to use the ControlClick in a "save as"-window to select the path of where to save the document.
      I found out that when in press the icon in front of the path, i select the path. So I am trying to press that button (i have attached a print screen of this window).
      While trying to figure out how i had to use the ControlClick, it said somewhere that i could us Au3Info to find out what title,... i should use with the ControlClick.
      The result of the Au3Info I have attached aswel.
      The ControlClick that i tried are these: (and none of these even move the mouse)
      ControlClick("[CLASS:ToolbarWindow32; INSTANCE:4]", "", "", "Left" ,1 ,10,10)
      ControlClick("[CLASS:ToolbarWindow32; INSTANCE:4]", "", "1001", "Left" ,1 ,10,10)
      ControlClick("[CLASS:ToolbarWindow32]", "", "", "Left" ,1 ,10,10)
      ControlClick("Save project as", "", "", "Left" ,1 ,10,10)
      I don't know what I am doing wrong.
      Should I use a controlID? is my windowtitle wrong?
      Can anyone help me?
      Thanks in advance,

    • Mercy
      By Mercy
      I need to add a column with data daily at the end of the column and calculate the difference between last two columns in put it in a seperate column. So i need to get the  position of the last column.
      Local $count = $oWorkbook2.Worksheets(1).UsedRange.Columns.Count MsgBox(0,"",$count) ; ***************************************************************************** ; Copy a single cell from another workbook. Pass the source range as object. ; ***************************************************************************** Local $oRange = $oWorkbook2.Worksheets(1).Range("A1:A15") _Excel_RangeCopyPaste($oWorkbook1.Worksheets(1), $oRange,"H1:H15") _Excel_RangeWrite($oWorkbook1, $oWorkbook1.Worksheets(1), "=C2-H2", "I2:I15", False) this code copies to a specific column H with a range 15. But i need to keep that column range to update automatically when columns are added.
    • Mercy
      By Mercy
      I inspect that Security tab in the webpage .I get the html element below:
      <td tabindex="-1" align="center" class="infaTabsetSimpleTabSetTop" nowrap="true" valign="middle" onfocus="app_details_browse.$47()">Security</td>
      If iam using the class name and trying to click the element. Iam getting "found" but cannot click as all the tab contains same class.
      If iam using the onfocus attribute which is different for every tab and trying to click the element, the element cannot be found.
      Local $oFound = "" Local $oBtns = _IETagNameGetCollection($oIE, "td") For $oBtn In $oBtns ;If String($oBtn.onfocus) ="app_details_browse.$47()" Then If String($oBtn.classname) = "infaTabsetSimpleTabSetTop" Then $oFound = $oBtn EndIf Next If IsObj($oFound) Then _IEAction ($oFound, "focus") _IEAction ($oFound, "click") MsgBox(16, "Success", "found") Else MsgBox(16, "Failed", "Not found.") EndIf

    • nacerbaaziz
      By nacerbaaziz
      I designed this code To help the blind
      Where screen readers users can  Identify the sefocus  item with audio files
      In this code i define some elements
      Unfortunately, I did not know how to define the other elements
      I am waiting for your initiative to  define the other elements
      Thank you in advance
      Global $CFocus = "" Func focus($window) Local $focusing = ControlGetFocus($window) Local $handle = ControlGetHandle($window, "", $focusing) If Not ($focusing = $CFocus) Then Switch StringTrimRight($focusing, 1) Case "button" If IsCheckBox($handle) Then play("checkBox") ElseIf IsRadio($handle) Then play("RadioButton") Else play("focus") EndIf $CFocus = $focusing Case "comboBox", "ListBox" play("list") $CFocus = $focusing Case "edit", "input" play("edit") $CFocus = $focusing Case Else play("focus") $CFocus = $focusing EndSwitch EndIf EndFunc ;==>focus Func IsCheckBox($ctrl_hwnd) $Style = _WinAPI_GetWindowLong($ctrl_hwnd, $GWL_STYLE) Return BitAND($Style, $BS_CHECKBOX) = $BS_CHECKBOX EndFunc ;==>IsCheckBox Func IsRadio($ctrl_hwnd) $Style = _WinAPI_GetWindowLong($ctrl_hwnd, $GWL_STYLE) Return BitAND($Style, $BS_AUTORADIOBUTTON) = $BS_AUTORADIOBUTTON EndFunc ;==>IsRadio Func play($name, $wait = 0) Local Const $SND_RESOURCE = 0x00040004 Local Const $SND_ASYNC = 1 ; Play the file - the script will pause as it sounds If $wait = 0 Then DllCall("winmm.dll", "int", "PlaySound", "str", $name, "hwnd", 0, "int", BitOR($SND_RESOURCE, $SND_ASYNC)) Else DllCall("winmm.dll", "int", "PlaySound", "str", $name, "hwnd", 0, "int", $SND_RESOURCE) EndIf Return 1 EndFunc ;==>play  

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.