Merge the same group of cells down a column quickly

I've got 900 rows of data in which each needs to have cells 1:3 merged and 
centered (so, a1:a3, b1:b3, c1:c3, etc).  Is there any fast way of doing this 
besides selecting the same three cells in each row, hitting Merge & Center, 
and then moving on to the next row?  Trying to create a macro has been 
fruitless and quite frustrating.

Im using 2007.

Thanks in advance!
-- 
Rich F
0
Utf
2/11/2010 2:55:02 PM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
4430 Views

Similar Articles

[PageSpeed] 42

in the name box put a1:c900. then "Merge Across", then r-click selection 
and format cells, alignment Horizontal Center.

Rich F wrote:
> I've got 900 rows of data in which each needs to have cells 1:3 merged and 
> centered (so, a1:a3, b1:b3, c1:c3, etc).  Is there any fast way of doing this 
> besides selecting the same three cells in each row, hitting Merge & Center, 
> and then moving on to the next row?  Trying to create a macro has been 
> fruitless and quite frustrating.
> 
> Im using 2007.
> 
> Thanks in advance!

0
Bob
2/11/2010 3:04:03 PM
What/Where is "Merge Across"?  Otto

"Bob I" <birelan@yahoo.com> wrote in message 
news:#3QW0tyqKHA.3800@TK2MSFTNGP06.phx.gbl...
> in the name box put a1:c900. then "Merge Across", then r-click selection 
> and format cells, alignment Horizontal Center.
>
> Rich F wrote:
>> I've got 900 rows of data in which each needs to have cells 1:3 merged 
>> and centered (so, a1:a3, b1:b3, c1:c3, etc).  Is there any fast way of 
>> doing this besides selecting the same three cells in each row, hitting 
>> Merge & Center, and then moving on to the next row?  Trying to create a 
>> macro has been fruitless and quite frustrating.
>>
>> Im using 2007.
>>
>> Thanks in advance!
> 
0
Otto
2/11/2010 3:23:30 PM
Here is a macro that will do that.  HTH  Otto
Sub Macro1()
    Dim rColA As Range
    Dim i As Range
    Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
    For Each i In rColA
       i.Resize(, 3).HorizontalAlignment = xlCenter
       i.Resize(, 3).Merge
    Next i
End Sub

"Rich F" <RichF@discussions.microsoft.com> wrote in message 
news:92E5CBF0-AD8F-4EBE-A728-1556A0135AD4@microsoft.com...
> I've got 900 rows of data in which each needs to have cells 1:3 merged and
> centered (so, a1:a3, b1:b3, c1:c3, etc).  Is there any fast way of doing 
> this
> besides selecting the same three cells in each row, hitting Merge & 
> Center,
> and then moving on to the next row?  Trying to create a macro has been
> fruitless and quite frustrating.
>
> Im using 2007.
>
> Thanks in advance!
> -- 
> Rich F 

0
Otto
2/11/2010 3:35:54 PM
Bob, 

What an elegant, simple solution.  You are awesome, sir.

Thanks!
-- 
Rich F


"Bob I" wrote:

> in the name box put a1:c900. then "Merge Across", then r-click selection 
> and format cells, alignment Horizontal Center.
> 
> Rich F wrote:
> > I've got 900 rows of data in which each needs to have cells 1:3 merged and 
> > centered (so, a1:a3, b1:b3, c1:c3, etc).  Is there any fast way of doing this 
> > besides selecting the same three cells in each row, hitting Merge & Center, 
> > and then moving on to the next row?  Trying to create a macro has been 
> > fruitless and quite frustrating.
> > 
> > Im using 2007.
> > 
> > Thanks in advance!
> 
> .
> 
0
Utf
2/11/2010 4:49:01 PM
Otto, 

O.k, I now see what I was doing wrong.  This works perfectly, too!

Thanks for teh speedy response.
-- 
Rich F


"Otto Moehrbach" wrote:

> Here is a macro that will do that.  HTH  Otto
> Sub Macro1()
>     Dim rColA As Range
>     Dim i As Range
>     Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
>     For Each i In rColA
>        i.Resize(, 3).HorizontalAlignment = xlCenter
>        i.Resize(, 3).Merge
>     Next i
> End Sub
> 
> "Rich F" <RichF@discussions.microsoft.com> wrote in message 
> news:92E5CBF0-AD8F-4EBE-A728-1556A0135AD4@microsoft.com...
> > I've got 900 rows of data in which each needs to have cells 1:3 merged and
> > centered (so, a1:a3, b1:b3, c1:c3, etc).  Is there any fast way of doing 
> > this
> > besides selecting the same three cells in each row, hitting Merge & 
> > Center,
> > and then moving on to the next row?  Trying to create a macro has been
> > fruitless and quite frustrating.
> >
> > Im using 2007.
> >
> > Thanks in advance!
> > -- 
> > Rich F 
> 
> .
> 
0
Utf
2/11/2010 4:52:01 PM
You're most welcome, have a great day!

Rich F wrote:

> Bob, 
> 
> What an elegant, simple solution.  You are awesome, sir.
> 
> Thanks!

0
Bob
2/11/2010 6:51:51 PM
Home tab, Alignment panel, and just below "Merge and center" on the 
pulldown. Rich is using 2007.

Otto Moehrbach wrote:

> What/Where is "Merge Across"?  Otto
> 
> "Bob I" <birelan@yahoo.com> wrote in message 
> news:#3QW0tyqKHA.3800@TK2MSFTNGP06.phx.gbl...
> 
>> in the name box put a1:c900. then "Merge Across", then r-click 
>> selection and format cells, alignment Horizontal Center.
>>
>> Rich F wrote:
>>
>>> I've got 900 rows of data in which each needs to have cells 1:3 
>>> merged and centered (so, a1:a3, b1:b3, c1:c3, etc).  Is there any 
>>> fast way of doing this besides selecting the same three cells in each 
>>> row, hitting Merge & Center, and then moving on to the next row?  
>>> Trying to create a macro has been fruitless and quite frustrating.
>>>
>>> Im using 2007.
>>>
>>> Thanks in advance!
>>
>>

0
Bob
2/11/2010 6:54:35 PM
Thanks Bob.  I hadn't seen that.  Otto

"Bob I" <birelan@yahoo.com> wrote in message 
news:eo1pou0qKHA.5736@TK2MSFTNGP05.phx.gbl...
> Home tab, Alignment panel, and just below "Merge and center" on the 
> pulldown. Rich is using 2007.
>
> Otto Moehrbach wrote:
>
>> What/Where is "Merge Across"?  Otto
>>
>> "Bob I" <birelan@yahoo.com> wrote in message 
>> news:#3QW0tyqKHA.3800@TK2MSFTNGP06.phx.gbl...
>>
>>> in the name box put a1:c900. then "Merge Across", then r-click selection 
>>> and format cells, alignment Horizontal Center.
>>>
>>> Rich F wrote:
>>>
>>>> I've got 900 rows of data in which each needs to have cells 1:3 merged 
>>>> and centered (so, a1:a3, b1:b3, c1:c3, etc).  Is there any fast way of 
>>>> doing this besides selecting the same three cells in each row, hitting 
>>>> Merge & Center, and then moving on to the next row?  Trying to create a 
>>>> macro has been fruitless and quite frustrating.
>>>>
>>>> Im using 2007.
>>>>
>>>> Thanks in advance!
>>>
>>>
> 
0
Otto
2/11/2010 10:37:37 PM
You're welcome too!

Otto Moehrbach wrote:

> Thanks Bob.  I hadn't seen that.  Otto
> 
> "Bob I" <birelan@yahoo.com> wrote in message 
> news:eo1pou0qKHA.5736@TK2MSFTNGP05.phx.gbl...
> 
>> Home tab, Alignment panel, and just below "Merge and center" on the 
>> pulldown. Rich is using 2007.
>>
>> Otto Moehrbach wrote:
>>
>>> What/Where is "Merge Across"?  Otto
>>>
>>> "Bob I" <birelan@yahoo.com> wrote in message 
>>> news:#3QW0tyqKHA.3800@TK2MSFTNGP06.phx.gbl...
>>>
>>>> in the name box put a1:c900. then "Merge Across", then r-click 
>>>> selection and format cells, alignment Horizontal Center.
>>>>
>>>> Rich F wrote:
>>>>
>>>>> I've got 900 rows of data in which each needs to have cells 1:3 
>>>>> merged and centered (so, a1:a3, b1:b3, c1:c3, etc).  Is there any 
>>>>> fast way of doing this besides selecting the same three cells in 
>>>>> each row, hitting Merge & Center, and then moving on to the next 
>>>>> row?  Trying to create a macro has been fruitless and quite 
>>>>> frustrating.
>>>>>
>>>>> Im using 2007.
>>>>>
>>>>> Thanks in advance!
>>>>
>>>>
>>>>
>>

0
Bob
2/12/2010 2:30:20 PM
Reply:

Similar Artilces:

Problem inserting a column!!!
Hello all. I just accessed a worksheet which Im trying to add a column to... I highlight the column which i want the new column inserted ...and when i try to insert a column it doesnt work! A warning message comes up stating that a column cannot be inserted until blank cells are removed from the right side of the sheet or something to that effect!? Whats going on??? Thank you. -- londonchick ------------------------------------------------------------------------ londonchick's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24529 View this thread: http://www.e...

Multiple Quick Parts
When I was using Outlook 2003 I had several toolbar each embedded with several macros to enter "pre-packaged" text and graphics into a message. Now with Outlook 2007, such toolbars have been replaced with Quick Parts. The problem is, I have way too many items I need to keep available. If I add them all as Quick Parts entries, I have to scroll and scroll and scroll and scroll to find the one I want. Previously, I all I had to do was click a single button and, Boom, there it was. Since it looks like I will be stuck with the scrolling to some degree, is it possible to put...

Sort
We've recently made a very long spreadsheet with about 80 columns. BAD things happen! When people do a lazy sort - that is, ctrl home, Sort and use the populated defaults, Excel only "selects" 64 columns. Of course this totally pooches the data and creates disconnects. No problems when the user selects the whole spreadsheet (by selecting the upper lefthand corner), sorts and fills in the criteria themselves. My question is, is this for real or am I imagining it? Is there a limit of 64 columns in Excel on that lazy sort? We're dealing with the workaround, I j...

Conditionally format points based on a third column
I would like the color of my data points to vary based not on their x or y value, but rather on a third column running alongside them. How can I do it? Assuming your third column is column "C" and that you would want to change the colors of the XY points based on the names Jim, Frank, or Kim, a VBA option could look like this (does not include any error handling): Sub ChangeXYColors() Application.ScreenUpdating = False Dim Rng As Range Cnt = 1 For Each Rng In Range("C2:C10") ActiveSheet.ChartObjects("Chart1").Activate ActiveChart.Seri...

Include contact e-mail address column
Is there a way to include the e-mail address column in the contact list by account report? You will need Crystal Reports 9.2.2 installed on a client computer plus the Crystal Enhancements for CRM (from the CRM installation CDs) in order to modify reports. -- Matt Wittemann http://icu-mscrm.blogspot.com "Agnes" wrote: > Is there a way to include the e-mail address column in the contact list by > account report? Thank's Matt. "MattNC" wrote: > You will need Crystal Reports 9.2.2 installed on a client computer plus the > Crystal Enhancements ...

Multiple choices for a cell
I am creating a worksheet which deals with employee or job candidates qualifications. Each cell will hold the various details pertaining to a person's qualifications. Different people have different degrees. This will be all stored in a single cell and separated by a comma. Is it possible to create some sort of custom or drop down box which contains controls or check boxes so as to create a standardised list of qualifications the data entry clerk can choose from? For example, i would like a cell to have a drop down box, which is a field that displays the qualifications a particular gra...

Exchange user Administrative Group
Hello, We have 25 sites world wide. We close our office in Turkey and all user's inside this site moved to other site. The DC has been decomissioned. Now, after 3 weeks, we wanted to remove the site OU from AD. But, when I checked the user properties under Exchange Advance Tab in AD console, I found user is under the site OU that need to be remove. We would like to remove that site OU from Active Directory. Does it create problem?, when site OU deleted from AD? if yes, can we change it to the site where all the user's belongs currently? appreciate your advise and help. Yonkey...

Select range for function in a cell
Hi, is it possible to select create a formula in a cell. such as =SUM( and have the cursor be between the () so the user can select th appopriate range to enter? Or would this just be done separately. (Since one would need to know the range and WHERE to put the formul in..ie. what cell) similar to if you hit the autosum button but ther are no numbers above or the left, it simply waits for the user t select a range and then hits enter. THanks! -- Message posted from http://www.ExcelForum.com Hi if I understood you correctly: No -- Regards Frank Kabel Frankfurt, Germany > Hi, is it p...

help with spliting data cells
I am looking for assistance with splitting data between cells. I have a column of data (first (space) last name). I need to split that column into two seperate columns so each name value is in it;s own cell and i end with 2 seperate columns. (john smith) to (john) (smith) Please advise if this is easy and can be done. Thanks, Phil pcavalcanto@Aol.com -- pcavalcanto ------------------------------------------------------------------------ pcavalcanto's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32212 View this thread: http://www.excelforum.com/showthread...

Copy and Paste in Excel, copies cell and formula, but shows same v
Excel 2003 with 2007 converters installed. Not constant problem, but have seen it once before, can't remember how to stop it happening. I have a bank forecast which I have a running total column, against a individual line item list. Noddy stuff. Problem. If I make changes to the forecast I need to recopy down the running total formula to refresh the running total when it doesn't pick up the additions, usual when a cut has been pasted in. Whilst it allows me to dragdown or copy and paste the formula and the new cells take on the correct line/column numbers to do thei...

Is there a fix for incorrect duplex printing of mail-merge postca.
Printing mail-merge postcards on a DUPLEX printer is not correct. We print monthly invitations. In P'03, the front of a postcard to each recipient prints on the opposite side of the same card as the message. It does not in P'07. (The front AND back of the card print on the first side [with the front and back of the next addressee]. The following 2 addressees appear on the back. This will work correctly only IF, there is no personal note to the addressee on the back of the card AND the number of addressees is divisible by 4. We have to keep 2 computers in my office unconve...

I need more columns
What if you need more than 256 columns? I cannot link the worksheet pages either because I am averaging the numbers 256 is the limit in Excel. You can write an average formula, though it's more work, e.g. =SUM(Sheet1!A1:A500,Sheet2!A1:A500)/COUNT(Sheet1!A1:A500,Sheet2!A1:A500) On Sat, 22 Jan 2005 09:57:04 -0800, "dilbert" <dilbert@discussions.microsoft.com> wrote: >What if you need more than 256 columns? >I cannot link the worksheet pages either because I am averaging the numbers In fact you don't need to "roll your own" average. this for...

displaying a comment only when the cell is selected #2
I'd like to insert a comment in a certain cell, so that when that cell is selected by a user, the comment is then displayed. I know this must be possible, but I haven't been able to figure it out.... I can show or hide comments in various combinations, but can't find a way to hide the comment UNTIL the cell is selected, and then display it. How do I do this please?! Thanks Hi Jeff, Try: Tools Options | View tab | select 'Comment indicator only" | OK --- Regards, Norman "Jeff" <no_em@ilplea.se> wrote in message news:JdidnecOi7ehGVbfRVnyhg@eclip...

How to have multiple columns in excel that will expand and colaps.
I have a large about of tiered data, multiple columns, that I would like for the users to be able to expand and colapse on by each column level to view that level of data. Is there a method of doing this. It's hard to understand what you are wanting from what you say. Provide some detail about what the data looks like at the start, then what it looks like after expanding and then what it looks like after collapsing. Don't attach a file to your post. Just describe what you have and want. HTH Otto "columnhelp" <columnhelp@discussions.microsoft.com> wrote in me...

Last Cell #2
I know this has been asked before but I cant find what I want on Google. I need to get the address or row number of the first empty cell in column A, either will do, I need to use VB to copy and paste data from ten sheets into one. A formula or a VB way would be great, anyway of identifying the first empty cell. This cell may be empty or contain a null string. Any help would be reaaly good. TIA Alan. to find the last row x=cells(rows.count,"a").end(xlup).row+1 to find the next empty x=cells(1,1).end(xldown).row ' or address -- Don Guillett SalesAid Software donaldb@281.com &...

duplicate cells linearly?
Hello all - Is there a way to duplicate a formula in cells were the row reference grows linearly, instead of simply the number of cells? For instance, if you have in Sheet2 the formula Sheet1!$c1 and you duplicate it into rows below, you get Sheet1!$c2 Sheet1!$c3 Sheet1!$c4 Sheet1!$c5 However, we would like to do : Sheet1!$c4 Sheet1!$c8 Sheet1!$c12 We have a spreadsheet composed of two worksheets. In the first worksheet there are cells which are summed every 72 rows. We want those summed values to appear in worksheet 2, in one row after the other. Since we have a lot of summed cells, i...

Cell Not working
I have office 2002, my windows is home xp. Last week we had to reformat my xp, got a hold of a nasty worm! I just use a chart to display numbers in series. Now when I try it from an old sheet it inserts the number as a date? I keep trying to change the cell to GENERAL, but it dosen't work. Can't understand it. When you say you keep trying to re-format the cell as General, what result do you get? If I do it on a date cell (re-format) I get the date code (i.e. the value of the date). Ex. cells has "03-01-11", select cell and re-format to General = 37632. -- Regards; Ro...

delete row if given cell = 'X'
Is there an easy way to do the above. Thanks much Carl "MSNews" <nf@no.com> wrote in message news:%23Fj6fBvPKHA.764@TK2MSFTNGP02.phx.gbl... > Is there an easy way to do the above. > > Thanks much > > Carl > I'm not an expert but use data filter to select the rows, select and delete them. Try it with a trial sheet first though! Peter Several ways 1. data>filter>autofilter>filter on the x>delete 2. a looping macro from the bottom up sub delifx() for i=cells(rows.count,"a").end(xlup).row to 2 step -1 if lcase(cells(i,&qu...

using two cells 1 with total time n 1 with date how can i put the.
I'm trying to put the amount of time spent in to a seperate sheet (Cell # 2 column E ) from a sheet that has the dates in one cell (column A) and the time spent in another (column K) and the reason for the time spent in column B. On the seperate sheet I have to put the amount of time spent in column E and match with the date I have in column A on the seperate sheet to populate the chart that I'm making. The only thing that I have been able to do is create a formula to show the amount of time spent and for what reason but i can't match it to the date because the dates ...

How do I send a group list without the actual names showing?
Cindy <Cindy@discussions.microsoft.com> wrote: 1) Ask your question in the BODY, not the subject. 2) Put the addresses in the Bcc field. -- Brian Tillman "Cindy" wrote: > Thanks! ...

matching part numbers dispersed in two columns
Hello - I work with price lists in Excel. I want to match lists whos main columns are part numbers. Problem is that there are part number that do not match in between the two lists. For example, the part numbers column in one list (I'll call it "A")ma have the part numbers 72579, 72580, 72581, 72582, 72583. The par numbers column in the other list (I'll call it "B") may have 72579 72580, 72582, 72583, 72584. I want to match the numbers from list "B" to list "A". If Excel coul just line up the matching numbers on a new column (and leaving blan ...

How to swap rows and columns?
I had a heart attack last year, and my doctor told me to keep a journal of diet, exercise, etc. I'm using an Excel worksheet to do it. Each column is a day of the year, and the rows are items, like how far I walked that day, how much I ate, how much sleep, my weight, my blood pressure, etc. As I got in the habit of using it, I started adding other stuff, so now it's become an important repository of data for me. The problem is, I thought Excel had essentially unlimited rows and columns, and it turns out that it only has 256 columns, which isn't enough for a year. It has thousa...

details about quick+insertion provided in c library(1997)
hi i came to know that microsoft improved the efficiency of quick sort by using a cutoff of 8 elements and continuing with insertion sort then, do anybody have the details about it please contant me. i will try to find out <mahale.amit@gmail.com> wrote in message news:1144988612.281114.269970@z34g2000cwc.googlegroups.com... > hi > i came to know that microsoft improved the efficiency of quick sort > by using a cutoff > of 8 elements and continuing with insertion sort then, do anybody have > the details about it > please contant me. > You have the sou...

print title page setup/sheet can not enter cell in row/column lin
On page setup in the sheet page we can not enter cells into the print titles row or columns. Also the worksheet cell query on these lines will not open. Hi Are you trying do this from Print Preview page, or File|Page Setup? I think it's only possible from the latter. -- Andy. "don r" <don r@discussions.microsoft.com> wrote in message news:26C400D7-14A0-41F8-BBE6-8DF2F5579A30@microsoft.com... > On page setup in the sheet page we can not enter cells into the print > titles > row or > columns. Also the worksheet cell query on these lines will not open. &g...

lowest Digit Cell...
hello what's the formula to use when i have 20 cells with digits & i want to get the lowest digit on those 20 cells, in a different cell ? --- Message posted from http://www.ExcelForum.com/ i ment like If A1 is 8.02 A2 is 8.24 A3 is 9.79 A4 is 7.09 A5 is 7.15 & Cell C1 suppose to Show 7.09 --- Message posted from http://www.ExcelForum.com/ One way: C1: =MIN(A1:A5) In article <Pivotrend.ysouy@excelforum-nospam.com>, Pivotrend <Pivotrend.ysouy@excelforum-nospam.com> wrote: > i ment like If > > A1 is 8.02 > A2 is 8.24 > A3 is 9.79 > ...