Sign in to follow this  
Followers 0
footswitch

Excel COM Object: Select multiple contiguous ranges without merging them

8 posts in this topic

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.


Share this post


Link to post
Share on other sites



What exactly are you trying to do here?

Your example works perfectly.

Using the "union" you can sellect multiple ranges.

Share this post


Link to post
Share on other sites

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? :)


Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by Juvigy

Share this post


Link to post
Share on other sites

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.


Share this post


Link to post
Share on other sites

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

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  
Followers 0