Jump to content

Excel Macro to Autoit... Selecting multiple Rows?

Recommended Posts


i want to delete multiple rows from a given excel file, i have all start/end row numbers i want to delete...

In Excel VBA a macro records the following selection:


I have generated the string in an variable with all start/end row numbers in the same format...

$Rows2Delete = "9:20,22:33,35:46"

or what i need for my application:
$oExcel.Application.Range('"'& $Rows2Delete & '"').Select

This does not select anything, it only generates a "==> The requested action with this object has failed."

Do i oversee any little syntax problem or is it not possible to select multiple rows in different places via autoit to delete them at the same time?



Link to post
Share on other sites

Well it works to select a single row "range" and delete it.

Example code that works:

#include <Excel.au3>
$oExcel = _ExcelBookNew()
_ExcelWriteCell($oExcel, "XXX", "A1:H20")

But my problem is i have a big excel file with many similar looking tables where i need to delete a few specific ones. I have all the code written to check each start/end row of this tables i need to delete. The problem is i need to delete all tables i want to purge at once because if i delete the first one, the start/end row for the next changed with this purge...

Excel VBA as i wrote it above just does select multiple rows (seperate with spaces etc.) with just that simple line of code:


This selects Row 9 to 20, 22 to 33 and 35 to 46.

Is it really possible that i can´t send this via Autoit?

Link to post
Share on other sites

I really don´t understand how the UDF could change anything with the error selecting multiple rows via COM...

#include <IE.au3>

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1

$oExcel.Application.Range("1:1").Select ; this works

$oExcel.Application.Range("1:1,3:3").Select ; this does not

This is completly without the excel UDF and the syntax of the second selection throws an COM Error that does not tell my anything :)

--> COM Error Encountered in test.au3

----> $IEComErrorScriptline = 10

----> $IEComErrorNumberHex = 80020009

----> $IEComErrorNumber = -2147352567

----> $IEComErrorWinDescription =

----> $IEComErrorDescription =

----> $IEComErrorSource =

----> $IEComErrorHelpFile =

----> $IEComErrorHelpContext = 0

----> $IEComErrorLastDllError = 0

Link to post
Share on other sites

What does ,in a limited fashion, work is:




Deletes rows 6, 7&8. It only works on consecutive rows.

I got it by using the Excel macro recorder, copy the code into scite & 'kick it around' 'till it looks like au3.

The Excel macro recorder will not let (me) select multiple rows unless they are consecutive. (Excel 07)

I tried:






It worked, but unfortunately after row6 was deleted, row9 wasn't the same row9 that I was after anymore :)

#include <GUIConstantsEx.au3>
#include <Excel.au3>
$oExcel = ObjGet("", "Excel.Application")
If @error Then
MsgBox(0, "", "You need to have Excel running for this to work", 4)
GUICreate("x", 80, @DesktopHeight * .925, @DesktopWidth * .75, 10)
WinSetOnTop("x", "", 1)
$label3 = GUICtrlCreateLabel("", 4, @DesktopHeight * .875 - 70, 72, 25)
$copy = GUICtrlCreateButton("copy", 10, (@DesktopHeight * .165 + 10), 60, 25)
GUICtrlSetState($copy, $GUI_SHOW)
While 1
$nMsg = GUIGetMsg()
  Case $nMsg = $GUI_EVENT_CLOSE
  Case $nMsg = $copy
   $sheetname = _ExcelSheetNameGet($oExcel)
   GUICtrlSetData($label3, $sheetname)
   $goback = GUICtrlRead($label3)
   With $oExcel
    .ActiveSheet.Unprotect;sheet protection in place to save me from boo-boos during data entry
    .Sheets("Sheet3").Visible = True;unhide sheet that I wish to copy from
    .Sheets("Sheet3").Select;open the sheet

    _ExcelSheetActivate($oExcel, $goback);back to sheet in use
    .ActiveSheet.Protect;re-protect the active sheet

Edit: had a real stupid boo-boo in there. Fixed it.

Edit2: got it !




.Range("A14").Activate ;>>>>>>>>>>>>>>this one turns the trick. Much like you'd do it with the mouse.



Works with Excel & AI. The reason Excel macro recorder would not let me select non-consecutive rows is that I also had an individual cell selected (on several tries).

Edited by lorenkinzel
Link to post
Share on other sites
  • 2 months later...

I have a question about all this ... I am trying to do something very similar. I read a long sheet into an array and look for certain values in a column that identify the rows I want to delete. You can see what I am doing here (note: the autoit code tag button is not working for some reason):

dim $range=""

for $a=0 to ubound($dataArray)-1

if $dataArray[$a][1] == "State Province" Then

$range &= $a&":"&$a&","




MsgBox("","This is the range: ",$finalRange)

The message box appears to show the results I want based on your example above: 5:5,50:50,90:90,... etc. Then I use the following to try to select the range:


This action, however, does not work. Instead, I get the following message: "C:UsersRC01712DocumentsScriptsScrub2.au3 (51) : ==> The requested action with this object has failed.:"

Is it permissible to use a variable for the range as an argument in the selection? What am I doing wrong? Any help would be greatly appreciated.



Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Link to post
Share on other sites

I was interested in the answer to this topic so I did some testing and found a working property to delete multiple non-contiguous rows/columns.

To anyone interested, try the following:

.ActiveSheet.Range("Range").EntireRow.Delete [For Rows]

.ActiveSheet.Range("Range").EntireColumn.Delete [For Columns]

"Range" can be a correctly formatted range for non-contiguous rows or columns separated by a comma. "Range" can also be replaced by a variable.

@JFish: Try putting your $range value in quotes EDIT: Try $finalRange value in quotes not $range, after you remove the last comma.

Edited by Reg2Post
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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...