• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Keyboard shortcut to select column with merged cells

G

Gael

New Member
Hi All,

I've got a problem to select a single column if it contains a merged cell.
For instance cells A1 and B1 are merged, if I try to select column B using keyboard shortcut CTRL + SPACE, then both A and B columns are selected (which is not what I want).

I know I could unmerge the cells, but I'm working with people who don't see this as a solution, because they love merged cells ;).

By the way there's an Excel 2003 shortcut that works for deleting a single column even if it contains merged cells:
ALT + E + D + C + ENTER

Many thanks in advance,
Gael
 
Good day Gael and welcome to the forum

You are selecting merged cells so you cannot just select a part of the merged cells that's what merged does it MERGES THE SELECTE CELLS INTO ONE. If your pepole like merged cells so much they are fools and should not be using Excel.

Avoid merging cells

Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.

For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.

In addition, not all cell formats stick once you unmerge a cell.

You can't sort a column with merged cells.

You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.

You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!

Merging cells in columns and rows could lead to data loss, bad thing.

Formulas and Functions that refer to merged cells will not work, bad thing.

Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.

Center Across Selection is a far better alternative to merging.

To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.

You will get the desired look you want but without the merged cells problems.
 
Hi bobhc,

Thanks for your answer, however I was looking for a keyboard shortcut to select a column that contains merged cells (rather than a long list on how I shouldn't use merged cells).

Cheers,
Gael
 
Hello Gael -
What bobhc is saying is that this cannot be done if the column contains merged cells. Suggest you find a workaround. Most of the time, the merged cell look (so dear to your users) can be achieved with Centre Across Selection.

- juanito
 
Hi Juanito,

Actually you can select a column that contains a merged cell by just clicking on the column header with your mouse, and I was just trying to find a way to replicate this with a keyboard shortcut.
 
You'd have to use VBA I believe (I'm no VBA expert). But I continue to support bobhc's advise to avoid merging in (nearly) all cases. Another way to achieve similar effects to a merged cell is to use the camera tool.

- juanito
 
I agree that merged cells isn't a great way to format cells, but if you export reports from MS Reporting Services (which I use a lot at work) you'll most certainly get merged cells, and there's no way around that.

I've got the VBA code below, all it does is unmerge the cells and select the column, which not exactly what I want but it's the closest I could find:

Code: 
Sub sel_col()
Cells.MergeCells = False
Selection.EntireColumn.Select
End Sub
 
Hi SM,

I've tried that, but when I run the code, both columns are selected.
I wish it was that easy ;)
 
You may need to select the individual column again after demerging? (After demerging two columns are selected?)
 
Gael said:
Hi All,

I've got a problem to select a single column if it contains a merged cell.
For instance cells A1 and B1 are merged, if I try to select column B using keyboard shortcut CTRL + SPACE, then both A and B columns are selected (which is not what I want).

I know I could unmerge the cells, but I'm working with people who don't see this as a solution, because they love merged cells ;).

By the way there's an Excel 2003 shortcut that works for deleting a single column even if it contains merged cells:
ALT + E + D + C + ENTER

Many thanks in advance,
Gael
Click to expand...



Hello Gae
I appreciate your answer but this is not the subject is.
Can you please ans as per the subject line.

"HOW TO ""ADD"" A SINGLE COLUMN IF IT CONTAIN A MERGED CELL."

Please let us know the shortcut key.

Hope some1 ans shortly
Thanks in Advance
 
Hi Rehman,

If you want the answer to a different question than the one I originally asked, I'd imagine you could start a new thread?

Anyways the keyboard shortcut to add a new column is Alt + I + C. As far as I can tell it works whether there's merged cells or not.

Cheers,
Gael
 
Just Awesome !!

Sorry but the purpose is not solved yet
Purpose was ''HOW TO SELECT COLUMN" & "HOW TO HIDE COLUMN" if it contain a merged cell.
Please let us know above these two shortcut. ASAP

Thanks
Rehman
 
Gael said:
Hi Juanito,

Actually you can select a column that contains a merged cell by just clicking on the column header with your mouse, and I was just trying to find a way to replicate this with a keyboard shortcut.
Click to expand...

Dear All,

How to programmatically click on column's Heading to select that entire column?
without face merged cells?

Regards,
Chirag Raval
 
You must log in or register to reply here.
Back
Top