Free Microsoft Excel 2013 Quick Reference

Merge & Center

The Merger and Center button appears to be "turned off".
No matter which cell or range of cells I select, I am
unable to use this button.

Any suggestions would be great. Thanks......


Post your answer or comment

comments powered by Disqus
Office XP Excel:

I am trying to figure out how to use the Merge & Center button when I am
formatting a table in Excel. It only occurs if I am in the table layout or
editing a table. If I click on a normal cell which has no color the Merge &
Center button works fine.

Any ideas?

When setting up a "table" I repeatedly used the merge & center function. I
want to expand and edit the table but the merge & center button is not
functioning (is grayed out). Also, when I copy a number of rows (which
contain merged & centered cells) the paste results in the merged & centered
cells being copied without the merge and center. And...the merge & center
function remains unavailable.

In Sheet1, I have Cells A1:D1 "Merged & Centered" with bold letters for
the title of the page

I am trying to write a macro top copy and Paste the Text from A1:C1 to
Sheet2!D2 (after inserting a new column in D2)

The problem is that after pasting in D2, the contents of E2 and F2 are
also cleared as it seems because the copied area is merged over three
cells, the pasted area is pasted over three cells.

Is there a way I could make this only Paste in Cell D2

For reference, this is what I am using:
Sheets("Sheet1").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet2").Select
Range("D2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Thanks in advance

Cathy

When setting up a "table" I repeatedly used the merge & center function. I
want to expand and edit the table but the merge & center button is not
functioning (is grayed out). Also, when I copy a number of rows (which
contain merged & centered cells) the paste results in the merged & centered
cells being copied without the merge and center. And...the merge & center
function remains unavailable.

In Office 2003 the Merge and Center button do not function when the sheet is
protected while the unprotected cells do the same behavior, but if you move
or cut merged cells and paste to another unprotected location the merged
cells become unmerged and the target location becomes merged on an
unprotected area. This behavior is good when you need to have part of the
sheet protected and part unprotected. My suggestion for Microsoft for the
merge and center button: this feature needs to work in unprotected cells
while other parts of the sheet are protected. On Excel 2003 work in an
indirect way.

But in Excel 2007 this behavior was eliminated which is bad. When the sheet
is protected the merge and center button do not work on unprotected cells
while other parts of the sheet is protected.

I am a analytical chemist from the Food and Drug Administration and we work
with Excel Analytical Application extensively and our analyst need to have
part of the sheet protected and other unprotected in order to create
different analytical application using the USP and other company method for
food and drug analytical method.

We have done extensive research on spreadsheet analytical applications and
discover a way for our analysts to develop from a template their analytical
application which look similar to other analysts which make it easer to
verify by other analysts because all formatting properties are similar. The
merge and center button is essential when a template sheet form has protected
as well as unprotected cells. By doing this we can control better the
application development. We call this workbook files "single-user" (need 100%
verification by a second analyst each time a workbook file template is
developed) because the template in use only one time, when the analyst
complete the analytical method it personalized for that particular drug
analysis and is used only one time. The printed report is used as the
official document which can be used in a court of law. The file is save and
archived for future audits.

We also have "multi-user" applications (need validation documentation) which
can be use by more than one analyst. This type of workbook files the formulas
cells are protected while the data-entry cells are unprotected. Also these
types of workbook file are used in the pharmaceutical industries.

I have several publications (US FDA Laboratory Information Bulleting) on
this topics (single-user / multi-user). Upon request I can send them.

I hope you can understand the above situation with the merge and center
buttom. I not please give me a call, and we can discuss.

Dennis Cantellops, QAM
US FDA San Juan District
787-474-4795

I have Excel 2003. The Icon for Merge and Center does not work. It remains
gray. Any ideas on how to get it to work? I tried to add the icon on again
by customizing the formatinmg toolbar, but that icon did the same thing,
nothing.

Is there a keyboard shortcut for 'merge and center'?
If not, can one be created since it is very tedious
having to continuously mouse up to the top to hit
that button?:

Hello. Is it possible to create a macro button that merges and centers an specific column range. i.e. A2:A10?. Thanks.

I don't like it when other people use merge and center in their spreadsheets, but I find myself leaning towards doing it in one of mine. I'm sure most of you are aware of the issues using merge and center can cause, so I won't go into them.

Please take a look at the attached example and let me know if you have any suggestions for a different way to go about clearly identifying the "Subcontractor", "Materials", and "Labor" sections without using Merge and Center as the example shows.


1. Type text into a cell.
2. Select the cell and few more cells in the same row.
3. Select Home -> Merge & Center (in Alignment Group).
Or
Click the dialog box Launcher arrow symbol in the bottom-right corner of the Alignment Group to open the Format Cells dialog box -> Alignment tab.
Or
Press Ctrl+1.
4. In the Alignment tab in Text control section check the Merge cells box and click OK.

Hi all,

How we can automatically (with macro) merge & center cells with same value? For example if ranges A6, A7, A8 have the same value 09/20/06, find them & merge A6:A8 & get only one 09/20/06.

Thank you in advance

Hello Everyone!
Please guide me on "How to enable Merge & Center option which is grayed out in Table Style Light 1". Advance thanks.

I am trying to merge cell A1 and cell A2 into one cell. I select both cells
by selecting one and then hitting shift while I select the other but the
Merge & Center button remains greyed out. HELP

I am trying to merge cell A1 and cell A2 into one cell. I select both cells
by selecting one and then hitting shift while I select the other but the
Merge & Center button remains greyed out. HELP

The Merger and Center button appears to be "turned off".
No matter which cell or range of cells I select, I am
unable to use this button.

Any suggestions would be great. Thanks......

Hello

Once again I need some help. Am trying to use the change event to move the cursor to a new location depending what is entered into cell A2. A2 contains a date. I want to have the Change Event move the cursor to a new location depending on the month of the date.
With the current code it either will not let me into cell A2 to change, or the change event runs in an endless loop.

ALso, cell a2 has a field width of 3 so I have used the merge/center to spread it across a2:c2 for display purposes. Not sure if this makes a difference. Have tried setting the VRange to just A2 as well as A2:C2

Thanks for any help


	VB:
	
 Range) 
    Dim VRange As Range 
    Set VRange = Range("A2") 
    If Union(Target, VRange).Address  VRange.Address Then 
         
        Range("D2").Select 
        If Month(Range("A2")) = "7" Then 
            ActiveCell.Offset(5, 0).Select 
        ElseIf Month(Range("A2")) = "8" Then 
            ActiveCell.Offset(5, 1).Select 
        ElseIf Month(Range("A2")) = "9" Then 
            ActiveCell.Offset(5, 2).Select 
        ElseIf Month(Range("A2")) = "10" Then 
            ActiveCell.Offset(5, 3).Select 
        ElseIf Month(Range("A2")) = "11" Then 
            ActiveCell.Offset(5, 4).Select 
        ElseIf Month(Range("A2")) = "12" Then 
            ActiveCell.Offset(5, 5).Select 
        ElseIf Month(Range("A2")) = "1" Then 
            ActiveCell.Offset(5, 6).Select 
        ElseIf Month(Range("A2")) = "2" Then 
            ActiveCell.Offset(5, 7).Select 
        ElseIf Month(Range("A2")) = "3" Then 
            ActiveCell.Offset(5, 8).Select 
        ElseIf Month(Range("A2")) = "4" Then 
            ActiveCell.Offset(5, 9).Select 
        ElseIf Month(Range("A2")) = "5" Then 
            ActiveCell.Offset(5, 10).Select 
        ElseIf Month(Range("A2")) = "6" Then 
            ActiveCell.Offset(5, 11).Select 
        End If 
         
         
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi:

I will be working with a workbook that has two worksheets with lots of borders around specific cells, some merged/centered and some just regular cells. This was set up this way by someone else who is particular about the printing and THAT is important. Meanwhile, it's a PITA for me to work with onscreen because the cell with the cursor isn't always obvious. I suspect that others who will eventually be using this workbook will find the same annoyance.

I have thought of two ways to overcome that annoyance:

First: Turn off the borders formatting and store the settings in another worksheet. I can probably do this with a LOOP going through cell by cell, and I actually think I could figure it out myself. HOWEVER, in keeping with Dave Hawley's concept that loops can be avioded 80% of the time... is there a better (and faster executing) way to do this without a loop?

Second: change the way the active cell looks... and embarassingly enough I don't know how to do this. If I knew, I could change it on workbook activation and change it back on deactivation.

Thanks!
Keith

I work in sales and I'm trying to create a shortcut for my Excel based Product Order Form.

I want to create a dropdown box in A1 that has all of my customer names available (ex. Customer1, Customer 2, etc) chosen from the Range B1:E1. After selecting one of the customer names, I want cell Range A2:J7 below to immediately populate all of this customer's shipping info. I have already entered this shipping date elsewhere on the worksheet and named the Range after the customer (ex, Customer1).

When I used an {=INDIRECT(A1)} array to accomplish this, it works but the formatting is ugly. The array places a 0 into any unused box from the named range. I want the INDIRECT fx to perfectly copy all of the formatted text (including bold/underline/merge¢er) from my named range and not place 0s into unused cells.

How can I make this work?

If anyone can point me to a resource for sample code or examples to help with the following I would appreciate it.

Every month I download a report from a state agency that I need to reformat, create charts from and send reminder emails based on some of the data. The report is downloaded as an EXCEL workbook containing a single worksheet. The worksheet is preformatted as a printed report. I have no control of this formatting and I can not get the data as a .csv file. The report contains 3 sections. See detailed report description below.

What I want to do:
1. Is copy the 3rd section of the report to a new worksheet.
2. Sort the new worksheet by name, and date.
3. Filter some names based on resource type.
4. Create a new file, which will be attached to an email to my boss for follow-up.

Originally I used the built-in EXCEL macro recording function and this worked for a couple of months. Then the number of rows in the 2nd section varied and my generated worksheet failed to sort correctly, and I lost the column headings so my filter no longer worked. The results were not something I could forward. I do not know how to modify the macro to search for the literals which end section 2 or start section 3 so I can copy the correct data to the new worksheet. I am not a VBA programmer, just an old-fart trying to do some administrative volunteer support for an important program, and really do not want to become one.

Detail Report Description:
Section 1: Headings –contain fixed headings in merged & centered cells.
Row 1 – Report heading 1 in merged cells A1–I1
Row 2 – Report heading 2 in merged cells A2-I2
Row 3 – Report heading 3 in merged cells A3–I3
Row 4 – Blank row
Row 5 – Organization information in cell A5 and merged cells B5–E5. Run by information in F5 and merged cells G5-H5.
Row 6 – Resource information in cell A6 and merged cells B6–D6.
Row 7 – Start of reporting period information in cell A7 and merged cells B7–D7.
Row 8 – End of reporting period information in cell A8 and merged cells B8–D8.
Row 9 – Blank row.
Section 2: People who have submitted timesheets during the reporting period.
Row 10 - Blank row.
Row 11 – Section Headings in merged cells A11-B11, C11-E11, F11-G11, single cell H11, & merged cells I11-J11
Row 12 – Start of data for this section. The data fields map to the column heading above.
The number of rows in this section varies from 1 to over 300.
Last row of section contains literal “Resource Totals:” merged into cells A?-B?.
Section 3: People who are missing timesheets for the reporting period.
The section starts with a blank row.
Next row contains section heading, starting with the literal “Resources Missing Period Timesheets” merged into cells A?-C?.
Next row contains column headings for the section; starting with the literal “Contact Name” merged into cells A?-C?.
Next row = Start of data for this section. The data fields map to the column heading above.
The number of rows in this section varies from 1 to the end of data. There is not a terminating literal.

One of our Club members has a workbook with 31 sheets. He has used Merge &
Center in several locations on several sheets. When he searches (Ctrl+F)
words within the Merged cells are never found. I have easily recreated the
situation on a workbook with only three sheets. The words in the merged cells
are not found.

Any solutions?
--
Chuck Davis
Publisher/Editor
Anthem Compendium
Sun City Anthem Computer Club newsletter. Information provided on an "as is"
basis. You must evaluate its applicability to your particular situation.

I'm working in Excel 2003 and am having some problems printing borders.

The tables are very large and have merged/centered column headings. Borders
are located around the column headings and along the left, right, top, bottom
of the table. The far-most column border on the right of the page doesn't
print even though it definately is there.

Can anyone please help me??? Thanks

Nicole

Hello,

I am using XL2003 on WinXP.

I have a concatenation formula that pulls text from 2 other worksheets. All
cells involved are formatted with Merge & Center and text wrap. The result
is that the first cell brought in is formatted correctly (text wraps), the
second cell loses the wrap.

Is there any way to force the text wrapping on the results?

Thank you for any information you can provide,
Diane

Inspired by Peltiers wonderful Dynamic charting tutorial
<http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html>, I
started to wonder how feasible it would be to create a set of rules
that would allow me to dynamically create a table based on exported
data of variable row count, column count, etc. I'm not really 'fishing
for code' here, as much as fishing for insights on how this would play
to automation/excel's strengths and weaknesses.

By table, I mean a presentable table ultimately destined for inclusion
in a printed report - thin borders for individual cells, thicker
borders denoting groups of information, merged centered column headers,
etc.

Currently I export the results of a crosstab query from Access 2002
onto an Excel 2002 worksheet, let's call it "SourceData". Another
worksheet ("Tbl4Export") contains formatted tables linked to the data
on "Sourcedata."

The data are very variable - It involves schools surveyed over the span
of several years, broken down by grade.

A typical table might look like (this is a stripped down version, so
text wrapping doesn't bone me; it could have up to 4 grades)

"Sch" = School Data
"St" = State Data
02 = Year 2002 etc.
Var = Variable
Pretending A1:I1, A7:I7 has an uninterupted border.

A B C D E F G H I
______________________________________________________
1|_Section Description 1_______________________________|
2| | Grade 6 | Grade 7 |
3| |Sch02|Sch03|Sch04|St 04|Sch02|Sch03|Sch04|St 04|
4|Var1 | 6.0| 6.5| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2|
5|Var2 | 4.0| 3.5| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2|
6|_____|_______________________|_______________________|
7|_Section Description 2_______________________________|
8| | Grade 6 | Grade 7 |
9| |Sch02|Sch03|Sch04|St 04|Sch02|Sch03|Sch04|St 04|
10|Var3 | 6.0| 6.5| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2|
11|Var4 | 4.0| 3.5| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2|
12|_____|_______________________|_______________________|

But say the School has missed a couple of years in the survey or didn't
do all grades one year, and also had an extra variable they were
measuring:

A B C D E F G H I
______________________________________________________
1|_Section Description 1_______________________________|
2| | Grade 6 | Grade 7 | Grade 9 |
3| |Sch03|Sch04|St 04|Sch02|Sch04|St 04|Sch04|St 04|
4|Var1 | 6.0| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2| 9.3|
5|Var2 | 4.0| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2| 8.1|
6|_____|_________________|_________________|___________|
7|_Section Description 2_______________________________|
8| | Grade 6 | Grade 7 | Grade 9 |
9| |Sch03|Sch04|St 04|Sch02|Sch04|St 04|Sch04|St 04|
10|Var3 | 6.0| 6.5| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2|
11|Var4 | 4.0*| 3.5| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2|
12|Var5 | | 1.5| 2.5| n/a | 6.0| 6.9| 3.2| 5.2|
13|_____|_________________|_________________|___________|
14|*Data categories var4 and var5 combined in year 2003 |
15|_____________________________________________________|

~On the Sourcedata sheet, the original data looks like this:

A B C D E F G H I J
1 Sec1 Section Description 1
2 grLbl Grade6Grade6Grade6Grade7Grade7Grade7Grade9Grade9
3 SchYr Sch03 Sch04 St 04 Sch02 Sch04 St 04 Sch04 St 04
4 v1 Var1 6.0 7.5 7.2 8.0 7.5 7.6 9.2 9.3
5 v2 Var2 4.0 4.5 5.2 5.0 6.5 5.6 7.2 8.1
6 Spacer
7 Sec2 Section Description 2

8 grLbl Grade6Grade6Grade6Grade7Grade7Grade7Grade9Grade9
9 SchYr Sch03 Sch04 St 04 Sch02 Sch04 St 04 Sch04 St 04
10 v3 Var3 6.0 6.5 7.5 7.2 8.0 7.5 7.6 9.2
11 v4 Var4 4.0* 3.5 4.5 5.2 5.0 6.5 5.6 7.2
12 v5 Var5 1.5 2.5 n/a 6.0 6.9 3.2 5.2
13 Spacer
14 v45lbl *Data categories var4 and var5 combined in year 2003

Note that in column A, I have some variable codes that can be used to
help differentiate/demarcate sections (i.e. "grLbl" indicates that row
contains grade labels).

Are there some elegant, easily adaptable approaches to "dynamic
tables"?

I'd love to put together a set of rules in VBA to programatically
address stuff like (a hypothetical list):
A)merge/center-across-selection the grade columns, apply heavy outline
to grade section borders, as appropriate (how to detect what defines a
"section"? Identical cell values?)
B}Make Section description rows grey, while
B)Always make the state data column yellow
C)Conditionally center some data (i.e. the "n/a" for variable 5 in
2002)
D)Separate each sections by a heavy border (i.e. on the second example,
the 6th grade data would have heavy border around ranges B2:D2, B2:D6,
B8:D8, B8:D13)
E)Adjust column width/row heighth to compensate for charts with fewer
columns (so sizes still mesh nicely with predetermined format in word?)
F)Be easily adjustable for the formatting whims of different schools?

I have so far had a lot of luck hiding table rows and columns via VBA,
but have gotten frustrated with things like disappearing cell borders
(oh, so A1 had a right border, but B1 didn't have a left border), the
irrational logic of: hiding columns + adjacent merged sections =
kablooey, the ease of adding new columns/rows with correct formatting
already in place, changing color schemes without individually selecting
different sections.

Wow. As Gramdpa Simpson said, "Anyway, long story short... is a phrase
whose origins are complicated and rambling..."

I realize I am asking about 20 different specific questions, but I'm
really not trying to get y'all to do my work for me. Mainly I'd love
tips on how to better understand my question, links to folks that have
discussed this before, the order I should consider tackling problems
("Do the borders last."), code snippets as a guide (I'm fairly code
savvy), grandiose yet vague theories...

Thanks,

Taylor Bryant

Is there a keyboard shortcut for the 'merge & center' function.
If there isn't, how do I create one?

thanks


No luck finding an answer? You could always try Google.