How to Merge Cells using VBA without losing underlying data

xlquest · Mar 17th 2020

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.

xlquest · Mar 17th 2020

On second thoughts, is there a way to achieve results of first method "manually" without using Format Painter?

TomC86 · Mar 18th 2020

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

Code

Option Explicit

Sub Merge_Ranges()
    Dim sht, tempSht As Worksheet
    Dim rng, Cell As Range
    Dim lRow, i As Long
    
    Application.ScreenUpdating = False
    Application.StatusBar = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    
    Set sht = Worksheets("Sheet1") ' Change your sheet name as required here
    lRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    Worksheets.Add.Name = "Temp" ' create a temporary worksheet to merge the cells
    Set tempSht = Worksheets("Temp")
    Set rng = tempSht.Range("A2:A" & lRow)
    
    i = 0
    
    If sht.AutoFilterMode = False Then
        sht.Range("A1:C" & lRow).AutoFilter 'adds filter if no filter
    End If
     
    On Error Resume Next
    sht.ShowAllData 'clears any filtering that may be applied
    On Error GoTo 0
    
    tempSht.Range("A1:C" & lRow).Value = sht.Range("A1:C" & lRow).Value 'copy the table from the original sheet to the temp sheet
        
    For Each Cell In rng
        If Cell.Offset(1, 0) = Cell Then
            i = i + 1
        Else
            tempSht.Range("A" & Cell.Row - i & ":A" & Cell.Row).Merge 'merges cells in column A
            tempSht.Range("A" & Cell.Row - i & ":A" & Cell.Row).VerticalAlignment = xlCenter
            tempSht.Range("B" & Cell.Row - i & ":B" & Cell.Row).Merge ' merges cells in column B
            tempSht.Range("B" & Cell.Row - i & ":B" & Cell.Row).VerticalAlignment = xlCenter
            i = 0 'resets to 0 to allow next ISD Code to be looked at
        End If
    Next Cell
    
    tempSht.Range("A1:C" & lRow).Copy
    sht.Range("A1:C" & lRow).PasteSpecial xlPasteFormats 'pastes the table back into your main sheet from temp using paste formats
    sht.Range("A1:C" & lRow).Borders.LineStyle = xlContinuous
            
    tempSht.Delete 'deletes the temp worksheet
        
    Application.ScreenUpdating = True
    Application.StatusBar = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
End Sub

Display More

xlquest · Mar 18th 2020

Hi Tom,

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

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!

**royUK** · Mar 18th 2020

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.

xlquest · Mar 18th 2020

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!

**royUK** · Mar 18th 2020

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

xlquest · Mar 18th 2020

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!

**royUK** · Mar 18th 2020

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

xlquest · Mar 18th 2020

Quote from royUK

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.

trunten · Mar 18th 2020

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

TomC86 · Mar 18th 2020

What Excel version are you using? I’m pretty sure you can get a very similar result with a pivot table in 365 using Dax.

xlquest · Mar 19th 2020

Quote from TomC86

What Excel version are you using? I’m pretty sure you can get a very similar result with a pivot table in 365 using Dax.

Excel 2016

Many thanks to Roy, TomC86 and trunten - for trying to help!

How to Merge Cells using VBA without losing underlying data

Participate now!

Share

Tags

Users Viewing This Thread