Click here to Skip to main content
13,044,539 members (58,452 online)
Click here to Skip to main content
Add your own
alternative version


1 bookmarked
Posted 13 Dec 2010

Tip: Perform a Merge and Center With Excel Automation

, 19 Dec 2010
Rate this:
Please Sign up or sign in to vote.
You know that little "Merge and Center" button in Microsoft Excel? Here's how to 'click' it in C# with Excel Interop (and this carries over to VB etc, too).
Say you have an Excel.Range variable called, e.g., MergeRange that represents the range of cells you would've otherwise highlighted had you been working directly with an open instance of Excel.

Now suppose we want to Merge and Center the text that is in the range. How do we do this? By the below:

public void MergeAndCenter(Excel.Range MergeRange) {
            MergeRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            MergeRange.VerticalAlignment = XlVAlign.xlVAlignBottom;
            MergeRange.WrapText = false;
            MergeRange.Orientation = 0;
            MergeRange.AddIndent = false;
            MergeRange.IndentLevel = 0;
            MergeRange.ShrinkToFit = false;
            MergeRange.ReadingOrder = (int)(Constants.xlContext);
            MergeRange.MergeCells = false;

Ta-da! Works with the Microsoft Excel v12.0 and above object libraries.

You know how I did this? I opened up an instance of Excel on my own, typed in some text and performed a merge and center -- all while recording a Macro.

Then I just did a 'View Code' (on the Developer tab -- you can show it using the Ribbon Orb > Excel Options and then somewhere there's a check box that says "Show the Developer toolbar" -- and there's also Record Macro/Stop Macro buttons there) and then opened up the VBA module corresponding to the Macro.

I then translated the VBA code to C# equivalents.

Bonus tip: Now you don't have to Google search for how to do something with Excel Interop -- just record a macro, do it by hand, stop the macro, view its code, translate to your interop language and voila!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Brian C Hart
Software Developer (Senior) Corrugated Technologies, Inc.
United States United States
From Fridley, Minnesota and I like computer programming! When I got started, I was working mostly with Windows GUI programming in C/C++. Then later on I worked with COM/DCOM for a school internship. I used COM/DCOM to write an ad hoc cluster server and job-running environment for a cluster of 24 Windows-based high-end visualization workstations. I moved on to C# and have been working in C# and Windows Forms ever since. I have yet to embrace Silverlight Smile | :)

You may also be interested in...

Comments and Discussions

GeneralReason for my vote of 5 Thank you. Someone was asking how to... Pin
Walt Fair, Jr.13-Dec-10 13:52
subeditorWalt Fair, Jr.13-Dec-10 13:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 19 Dec 2010
Article Copyright 2010 by Brian C Hart
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid