PDA

View Full Version : VBA code to merge cells



rockybalboa
09-25-2013, 10:18 PM
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
09-26-2013, 12:24 AM
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
09-26-2013, 12:31 AM
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
09-26-2013, 12:32 AM
any solution for this???

rockybalboa
09-26-2013, 01:20 AM
Great .
Thanks

p45cal
09-26-2013, 01:43 AM
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
09-26-2013, 03:15 AM
Thanks

SamT
09-27-2013, 07:17 AM
Rocky,

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