footswitch Posted January 11, 2012 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.
Juvigy Posted January 11, 2012 Posted January 11, 2012 What exactly are you trying to do here? Your example works perfectly. Using the "union" you can sellect multiple ranges.
footswitch Posted January 11, 2012 Author 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?
Juvigy Posted January 12, 2012 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
footswitch Posted January 12, 2012 Author 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
Juvigy Posted January 13, 2012 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
footswitch Posted January 25, 2012 Author 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.
Juvigy Posted January 27, 2012 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
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