Excel COM Object: Select multiple contiguous ranges without merging them

[footswitch 1]

Hello guys,

I'm (sort of) desperately trying to select multiple contiguous ranges in Excel, without "merging" the selections into one big selection.

The final purpose is to merge individual selections, but all of the selections at once, which would save a lot of script execution time.

Take for example the ranges A2:C2 and D2:H2

If you take a look at the example, you'll understand my predicament:

#include <Excel.au3>

$oExcel = _ExcelBookNew()

$oExcel.Cells(1,1).Select ; reset selection

$oExcel.Range( _
                $oExcel.Cells(2,1), _
                $oExcel.Cells(2,3)).Select
MsgBox(0,"","selected columns 1 to 3")

$oExcel.Cells(1,1).Select ; reset selection

$oExcel.Range( _
                $oExcel.Range($oExcel.Cells(2,1),$oExcel.Cells(2,3)), _
                $oExcel.Range($oExcel.Cells(2,4),$oExcel.Cells(2,8))).Select
MsgBox(0,"","selected columns 1 to 3 and 4 to 8 using .Range")

$oExcel.Cells(1,1).Select ; reset selection

$oExcel.Union( _
                            $oExcel.Range($oExcel.Cells(2,1),$oExcel.Cells(2,3)), _
                            $oExcel.Range($oExcel.Cells(2,4),$oExcel.Cells(2,8))).Select
MsgBox(0,"","selected columns 1 to 3 and 4 to 8 using .Union")

$oExcel.Cells(1,1).Select ; reset selection

$oExcel.Union( _
                            $oExcel.Range($oExcel.Cells(2,1),$oExcel.Cells(2,3)), _
                            $oExcel.Range($oExcel.Cells(2,5),$oExcel.Cells(2,8))).Select
MsgBox(0,"","selected columns 1 to 3 and 5 to 8 using .Union"&@CRLF&"as you can see it didn't merge the selections into one, "&@CRLF&"because this time they're not contiguous.")

_ExcelBookClose($oExcel, 0, 0)
Exit

So, as you can see, selecting contiguous ranges results in a merge of the individual selections.

I received the suggestion of using the method get_Range() or the Worksheet.Range Property from Microsoft.Office.Interop.Excel, but they're talking about Visual Studio 2010. I don't know how to access that reference from AutoIt...

Any ideas?

Thanks in advance.

[Juvigy 49]

What exactly are you trying to do here?

Your example works perfectly.

Using the "union" you can sellect multiple ranges.

[footswitch 1]

This really isn't easy to explain with words.

In Excel:

- Ctrl+click+drag over A2:C2

- then Ctrl+click+drag over D2:H2

Now you have TWO distinct selections.

If you click the "Merge and Center" button, you'll have TWO merges, one for each selection.

The problem is, if I do it over COM, contiguous ranges turn into a single range.

And if I merge that selection, it becomes ONE big merge instead of individual merges.

Am I having any luck explaining this?

[Juvigy 49]

Not true. Check this out:

$oExcel.Union($oExcel.Range($oExcel.Cells(2,1),$oExcel.Cells(2,3)),$oExcel.Range($oExcel.Cells(2,5),$oExcel.Cells(2,8))).Select

$oExcel.Selection.Merge

[footswitch 1]

Those are NON-contiguous ranges.

2,1-2,3 ; 2,5-2,8

This is a contiguous range:

2,1-2,3 ; 2,4-2,8

[Juvigy 49]

Why do you need 2 ranges 1-3 , 4-8 when you can have 1 range 1-8 ?

This one ?

$oExcel = _ExcelBookNew()

$oExcel.Range($oExcel.Cells(2,1),$oExcel.Cells(2,3)).Merge

$oExcel.Range($oExcel.Cells(2,4),$oExcel.Cells(2,8)).Merge

MsgBox(0,"","selected columns")

Edited January 13, 2012 by Juvigy

[footswitch 1]

Sorry; because of some reason my profile stopped following all topics...

Yes, it's because I want to merge each range.

The reason why I wanted to do those merges all at once (select all the ranges separately and then merge) is because the merge operation is the slowest step of my script.

[Juvigy 49]

Strangely i recorded a macro that works, but when i converted it to Autoit - the merge is 1 cell - not 2 cells how it should be.

You can do the merging of contigious ranges one at a time:

$oExcel.ActiveSheet.Range($oExcel.ActiveSheet.Cells(2,1),$oExcel.ActiveSheet.Cells(2,3)).Merge

$oExcel.ActiveSheet.Range($oExcel.ActiveSheet.Cells(2,4),$oExcel.ActiveSheet.Cells(2,8)).Merge