How to Merge Cells using VBA without losing underlying data

  • Dear Experts,


    The need: I want to merge some cells with same data in a column so it looks more organized, at the same time I should be able to apply a filter on that column that can fetch all rows from other columns corresponding to merged cells. See two methods shown in attached GIF.


    The first method: I am merging cells using format painter by copying format of another set of merged cells from Column B to Column A. When I use this method, I can apply filter in Column A (ISD Code), and it fetches all corresponding rows from Column C (State). Also, notice that when the cells are unmerged, all underlying cells still have the data in them.


    The second method: I use the Merge Cells from Home Ribbon. In this case, I am prompted with a message that warns: "Merging cells keeps the upper-left value and discards other values". After clicking OK, the cells are merged. But when filter is applied, only one corresponding is fetched from Column C.


    Is there a way to achieve the first method using VBA, without having to use PasteSpecial?


    Thanks in advance.

  • Hey


    Most people would say avoid merged cells. That said, if its what you want...


    I am by no means an Excel wizard but this is how I have attempted your query. It basically copies your table to a new worksheet, does the merging of the cells and then pastes it back to your original sheet.


    I have built it to merge column B also so just copy that out if not needed. Example attached. Please use on a copy of your workbook and not the live version just in case.


    I haven't had long to do this so forgive the lack of error handling!


    Tom MergeCellsExample.xlsm

  • Hi Tom,


    Thanks for the attempt. your solution above will do the trick. :thumbup:


    I have to do this activity in 4000+ rows across 14 columns. If I use the method using PasteSpecial, it will take a very long time. That is why I am trying to avoid PasteSpecial or Format Painter. Do you know of a solution without using PasteSpecial?


    Thanks!

  • Merging Cells is definitely a bad idea. If you do want to continue then you could use the code above and instead of copy and pasting back row by row it might speed up if you copied all the data at once.


    Looking at the image though that is totally removing any chance of using the data as a database. An example workbook would be much better than a picture.

  • Hi Roy,


    See the attached file. Data in Column A and Column B are merged. However, the difference in merge comes into play when you try to filter data in both columns. When you filter for '+91' in Col A, 4 rows are fetched. But when you filter data in Col B for India, only one row is fetched.


    Also, when you unmerge Col A, you can see every cell has data. When you unmerge ColB, only two cells have data.


    I want to understand how can I achieve merge as done in Col A, without using Format Painter or Paste Special. Is that possible?


    Thanks!

  • That's why no-one who understands excel will never use merged cells. You are trying to use Excel as a database but you cannot do so with merged cells. This is how you data should be to act as a DataBase

  • Hi Roy,


    Indeed, that is the way I need data. And I agree with your direction of not using merged cells if data is compromised. My question still remains.


    In the file I had attached, Col A has data like the way you have represented in the file you attached - only difference is that it "looks" and "behaves (for navigation)" like merged cells. For filtering or for fetching data from cells, it still behaves like the file you attached. So, Col A in my file, can still be used like a DataBase with each cell in Col A still having its own data (i.e A3, A4 till A9, still has data in them) and also look more organized.


    Col B (from the file I attached) is how I don't want the data to be. In that column the data in cells are compromised (only B3 and B7 has data. data in b4 till b6 and b8 till B9 are lost) while merging.


    Still my question is - how can you achieve the merging like that in Col A (not compromising the underlying data), without using PasteSpecial or FormatPainter. Can that be achieved?


    P.S: I referred the steps 1 to 7 mentioned in this webpage - https://www.extendoffice.com/d…-filter-merged-cells.html to achieve the merging in Col A, of my excel file.


    Thanks!

  • That filtering requires you have the ExtendOffice addin, which I don't think is free.


    Also, the data is still in the merged cells because it was entered before merging

  • Also, the data is still in the merged cells because it was entered before merging


    Data is entered in each cells before merging.


    In the spreadsheet I attached, A3 to A6 is entered with "+91" and A7 to A9 is entered with "+31" before copying the format (using format painter) of a similar sized merged cell and applying the format to A3 till A6. When I use format painter, the merged cells A3 to A6 still retains underlying data.


    If I were to use "Merge Cells" on A3 to A6 and not the format painter trick, data from A4 to A6 is lost (after I confirm the warning message) and only data in A3 is left unharmed, after the cells are merged.


    My quest is to find out if you can do merging without using "Format Painter" or PasteSpecial (in VBA), still without losing underlying data.

  • I've never found a way to do that. I kind of concur with royUK tbh. Merged cells are evil and will play havoc with all sorts of things. However, I do see why you would like to just formt thing a bit more elegantly.


    Can't think of any way of acheving your aim without a pastespecial though. It doesn't have to be that onerous though. Here's a pointless little example that I've mapped the macro to CTRL+m


    Select a group of cells, press ctrl+m and away you go :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!