[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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


0
millos
Asked:
millos
1 Solution
 
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now