Jump to content

Recommended Posts

Posted

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.

Posted

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

Posted

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

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
  • 2 weeks later...
Posted

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.

Posted

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

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