Merge Macro

I'm trying to help someone in the office with the following problem. He has the following data in an Excel file:
C1   C2
ITEM      
1      
      
2      
      
3      
4      
5      
6      
7      
8      
9      
10      
C1 = Column 1
C2 = Column 2

He wants to merge Column A & B into one cell.  He has to manually merge each individual row by selecting the two cells, go to Format, Cells, Alignment, select Merge Cells and click OK.   It does the job, but he has to do it every time and he works with that file most of the day.  

When he tries to merge all the rows he gets the following message:
"The selection contains multiple data values.  Merging into one cell will keep the upper-left most data only."

Is there a way around this???  I tried recording a Macro and use a shortcut key of  CTRL + M.  When I hit CTRL+M it does only the cell that I created the Macro.  Is there a way to create a Macro that does several rows and when I hit the Shortcut Key do all them.

I hope I was clear…

Alex


millosAsked:
Who is Participating?
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.

criCommented:
First: Do not use merging of cells unless you are forced to it, too much problems with it.

Second: As loosing the content in B does not seem to be a problem: Why not simply deleting column B as whole, or if not appropriate, cells B1:B10 ?

If you want/need this macro: Replace the text of your recorded macro with this one, name does not matter.

Sub MergeCellsAiBi()
   
    Dim i As Integer, sCellsPerRow As String
   
   
    For i = 1 To 10
       sCellsPerRow = "A" & i & ":B" & i
       Range(sCellsPerRow).Select
       With Selection
          .MergeCells = True
       End With
    Next i
End Sub




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
tfspryCommented:
Hello Alex -

Your example above does not show what kind of info you have in col A & B, but
I'll assume that you want to combine col A with col B

Try this formula in Col C
=A5&" "&B5

The results would be ...

Col A      Col B        Col C
Bob        Smith       Bob Smith
Joe         Brown      Joe Brown

0
tfspryCommented:
Hello Alex -

Your example above does not show what kind of info you have in col A & B, but
I'll assume that you want to combine col A with col B

Try this formula in Col C
=A5&" "&B5

The results would be ...

Col A      Col B        Col C
Bob        Smith       Bob Smith
Joe         Brown      Joe Brown

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

calacucciaCommented:
Hi millos,

there is a lot more easy way to solve this.

Try to find the 'center across selected columns' or something like that.

I don't have the english version of office at home but I answered a similar question 4 days ago.

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10235366 


Reading it back again I see there are two options: MERGE ACROSS
         and CENTER ACROSS SELECTION

The first one will do what your collegue does manually (merging 2 cells into 1 but for each row)
The second one actually leaves the content of column 2 and just centers the first column over columns 1 and 2.

Bye, Geert
0
criCommented:
Yep, if it is only a matter of formatting, then you must reject my proposed answer and accept calacuccia's, as a.m. merging cells is troublesome.
0
MoondancerCommented:
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed.  Also a question is posted there specific to these changes that apply to the experts here.  Also, I am including the link to our All Topics, since many new ones have recently been added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
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.