VBA code to merge cells

[rockybalboa]

Hi,

Need a VBA code to merge all same values in Name column. Also after merging when i filter by any particular name it should show all its respective rows from column B and C.

Name	Ser	Class
Candide 2008	2008-2	A
Candide 2008		B
Candide 2008		C
Candide 2012	2012-1	A1
Candide 2012		A2
Candide 2012		B
Candide 2012		C
Candide 2012		D
Candide 2012		E

Thanks

[Jan Karel Pieterse]

This is not something you should try to solve using merged cells, Excel does not handle merged cells very well, especially when sorting. Also, the empty cells in col. B will not allow you to properly filter on "Ser", because the empty cells will not be shown

[Jan Karel Pieterse]

Clicked submit before finishing my message :-)
That being said, you could apply a conditional format to the first two columns which turns the fontcolor to white for all cells that have the same value in them as in the cell immediately above.

- On the Home tab, click the Conditional formatting dropdown, select "New Rule".
- Choose "Use a formula to...."
- Enter this formula in the box (I assume you have selected cells in columns A and B, starting from row 2, so cell A2 is the currently active cell):
=A1=A2
- Now click the format button and set the fontcolor to white.
- OK your way out of all dialogs

[rockybalboa]

any solution for this???

[rockybalboa]

Great .
Thanks

[p45cal]

Another way is to create a pivot table as in the attached (I'm using Excel 2003 at the moment and I've hidden part of the pivot table, this isn't necessary in Excel 2007 onwards). First you need to fill in a few blanks in your table so that it ends up like:

Name Ser Class
Candide 2008 2008-2 A
Candide 2008 2008-2 B
Candide 2008 2008-2 C
Candide 2012 2012-1 A1
Candide 2012 2012-1 A2
Candide 2012 2012-1 B
Candide 2012 2012-1 C
Candide 2012 2012-1 D
Candide 2012 2012-1 E


You can do this by selecting the ser column of the table, pressing F5, Special, blanks, OK, then WITHOUT changing the resultant selection, type the following on the keyboard:
=
cursor up key once
hold CTRL and press Enter.
After that, if you want, you can select the column again and copy/paste-special values to lose the formulae just added.

The pivot table has a drop down arrow for selcting the name(s) you want to filter for.

[rockybalboa]

Thanks

[SamT]

Rocky,

Can you use the Thread Tools button to mark this thread "Solved" if it is?