Excel: Merge multiple selections using R1C1 terminology?

Hello there,

I'm trying to merge multiple selections in Excel via VB/Macro.

Meaning that I want to perform multiple selections at once, and then merge those selections, not all into one, but into individual merges.

This is easily done manually, using Ctrl + click + drag I can perform multiple selections, and when I click the Merge button, each Selection becomes merged.

But when I do it on a Macro, it takes the top-left cell and the bottom-right cell of all selections, and turns it into a single, big selection.

I'm also using R1C1 terminology throughout the whole program, so I'm not working with column letters.

This is an example with two separate ranges, that becomes a single range when executed.

Range( _
    Range(Cells(1, 1), Cells(1, 3)), _
    Range(Cells(1, 4), Cells(1, 15)) _
    ).Select


Do you have any ideas? Thank you in advance for reading.
LVL 2
footswitchAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

krishnakrkcCommented:
Hi

Range make them continuous range. Use Union instead of Range

Union(Range(Cells(1, 1), Cells(1, 3)), _
    Range(Cells(1, 5), Cells(1, 15))).Select

Kris
0
footswitchAuthor Commented:
Hi,
Thank you for your reply.

It seems like in some cases it could work, but the cells are contiguous, so it is still joining the selections, like this:

Union(Range(Cells(1, 1), Cells(1, 3)), _
    Range(Cells(1, 4), Cells(1, 15)), _
    Range(Cells(1, 16), Cells(1, 21)).Select

So it's still merging all selections into one.

Any other suggestions?
Thanks in advance.
0
krishnakrkcCommented:
Hi,

That's a contiguous range.

MsgBox Union(Range(Cells(1, 1), Cells(1, 3)), _
    Range(Cells(1, 4), Cells(1, 15)), _
    Range(Cells(1, 16), Cells(1, 21))).Address

Kris
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

footswitchAuthor Commented:
I'm sorry, what I mean is, if I use Range("A1:C1,D1:H1") in an Excel Macro, and then Merge, it merges the selections individually.

I want and need a total of three things:
1. I need something that works outside of an Excel Macro (via COM Object);
2. I need to perform multiple selections at once because that would speed up the execution quite significantly;
3. I want to use R1C1 terminology (but just because I'd prefer not to use a workaround for that).

Thanks again.
0
krishnakrkcCommented:
Hi,

" I need something that works outside of an Excel Macro (via COM Object);"

This is not my area of expertise. I hope experts who follow this thread would guide you.

Kris
0
footswitchAuthor Commented:
Sure, I understand. Thank you anyway.

To anyone else that can provide some input:
I can translate most of the code from one side (Macro) to the other (AutoIt, which is VERY similar to Visual Basic).

BUT the Range parameter isn't accepting the string "A1:C1,D1:H1".
It accepts "A1:C1", however.
0
lxy_tonyCommented:
Hi,

The code below can work in my add-in. I think it can work in your COM project. But you need do exchange from column name to column index.

int row0=1;
int column0=1;
int row1=1;
int column1=5;

Excel.Range Range = (Excel.Range)objSheet.get_Range(objSheet.Cells[row0, column0], objSheet.Cells[row1, column1]);
Range.Merge(Missing.Value);
Range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
Range.EntireColumn.AutoFit();
0
lxy_tonyCommented:
You want to merge multiple selections at once. It is easy to get the row0, row1 and column0, column1 of selected range if you get all selected cells's column index and row index. You only need compare some intergers.

This function convert column name to column index. Maybe it is useful for you.
public static int ExcelColumnNameToIndex(string columnName)
        {
            if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+")) return 0;

            int index = 0;
            char[] chars = columnName.ToUpper().ToCharArray();
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[ i ] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            return index;
        }
0
footswitchAuthor Commented:
Sorry, I didn't get it.
Let's start over.

What I want to do is merge multiple individual ranges at the same time.

What I'm doing right now is merging one range at a time. Isn't that what you're replicating in your first comment?

Your second comment would eventually be the way to go, only if the Excel COM Object accepted a Range like "A1:C1,D1:H1" (that's what shows up in the Macro), which it doesn't.
It accepts, however:
Range(Cells(y,x),Cells(b,a))
Range("C3")
Range("B9:D7")

Finally, Union.(Range(Cells...),Range(Cells...)[, Range(Cells...), ... ]) only helps for non-contiguous ranges.
0
lxy_tonyCommented:
Hi,

I think I need explain for my comments "It is easy to get the row0, row1 and column0, column1 of selected range if you get all selected cells's column index and row index. You only need compare some intergers."

From range "A1:C1, D1:H1", you can get 4 cells (A1, C1, D1, H1). And you can computer the range should be merged is A1:H1. How we get A1:H1? We can compare the 4 cells' row index and column index, and get the Min and Max value of row index and column index. For "A1, C1, D1, H1", row range is from 1 to 1, column range is from A ro H. So you can use Rang(Cells(1,1),Cells(1,8)) or Range("A1:H1") to merge and only need merge once.
0
footswitchAuthor Commented:
I don't want to merge A1:H1.

I want to merge A1:C1 and D1:H1.

If you create a macro with the line:
Range("A1:C1,D1:H1").Merge
there you have it. Two separate merges.
But this is only working in an actual Macro, not via COM.
0
lxy_tonyCommented:
Sorry, I did not understant your problem before.

These code should working for you. You need add Microsoft.Office.Interop.Excel refrence to you project.
 
Excel.Application objApp = new Excel.Application();
objApp.Visible = true;

Excel.Workbook objBook = objApp.Workbooks.Add(Missing.Value);
Excel.Worksheet objSheet = objBook.Worksheets[1] as Excel.Worksheet;

Excel.Range Range = (Excel.Range)objSheet.get_Range("A1:C1,D1:H1", Missing.Value);
Range.Merge(Missing.Value);

Open in new window

0
footswitchAuthor Commented:
Hello again,
Thank you for your patience.
What do you mean "add Microsoft.Office.Interop.Excel refrence to you project"?

I'm scripting in AutoIt, so any experimental solution must be scripted with reference to a COM Object, or a DLL link, or something that doesn't depend directly on the language itself.

Do you have the source code for get_Range()?
0
lxy_tonyCommented:
Hello,

Please see my soure code attached. I write the test project using VS 2008, and it works.
I don't know how to do it in AutoIt. I hope my source code can help you find the get_Range().

 TestExcel.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
footswitchAuthor Commented:
I'm still trying to figure out a way of "converting" the code that lxy_tony provided.
I'm sorry that it's not helping me for my specific needs, but it's a solution written in an external language, so I thank him for the effort and accept the solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.