footswitch Posted January 11, 2012 Share Posted January 11, 2012 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:H2If 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) ExitSo, 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. Link to comment Share on other sites More sharing options...
Juvigy Posted January 11, 2012 Share Posted January 11, 2012 What exactly are you trying to do here? Your example works perfectly. Using the "union" you can sellect multiple ranges. Link to comment Share on other sites More sharing options...
footswitch Posted January 11, 2012 Author Share Posted January 11, 2012 This really isn't easy to explain with words.In Excel:- Ctrl+click+drag over A2:C2- then Ctrl+click+drag over D2:H2Now 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? Link to comment Share on other sites More sharing options...
Juvigy Posted January 12, 2012 Share Posted January 12, 2012 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 Link to comment Share on other sites More sharing options...
footswitch Posted January 12, 2012 Author Share Posted January 12, 2012 Those are NON-contiguous ranges.2,1-2,3 ; 2,5-2,8This is a contiguous range:2,1-2,3 ; 2,4-2,8 Link to comment Share on other sites More sharing options...
Juvigy Posted January 13, 2012 Share Posted January 13, 2012 (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 January 13, 2012 by Juvigy Link to comment Share on other sites More sharing options...
footswitch Posted January 25, 2012 Author Share Posted January 25, 2012 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. Link to comment Share on other sites More sharing options...
Juvigy Posted January 27, 2012 Share Posted January 27, 2012 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now