VBA Macro to unmerge empty merged cells

[tjdickinson]

I am looking for code that will automatically unmerge empty, horizontally merged cells so that column lines will be visible, but which will not unmerge vertically merged cells (all of which contain data). The code will be inserted into a macro which will execute a number of other formatting processes, some of which depend on the cells being unmerged.

So far, the code I have is:

Sub UnMergeCell()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
    If IsEmpty(cell.Value) = True Then
        cell.Style = "Note"     'I'm using this in testing just to see which cells are affected
      cell.UnMerge
    End If
    Next
End Sub

I had hoped that the text in the merged cells would prevent them from unmerging (thus the IsEmpty() function), but this code still unmerges all the cells. How can I fix the code so that only the empty horizontally merged cells unmerge? The main data range is B3:F15 on all sheets. A1:F1 are merged horizontally, but there is text in the merged cell.

 

[mumps]

My advice would be to re-design your sheet if possible so that you have no merged cells to begin with. You should avoid using merged cells because they almost always create problems for macros.

 

[rpaulson]

try this

If ActiveCell.MergeArea.Columns.Count > 1 And IsEmpty(cell.Value) = True Then

 

[tjdickinson]

My advice would be to re-design your sheet if possible so that you have no merged cells to begin with. You should avoid using merged cells because they almost always create problems for macros.

Thanks for your reply. I'm dealing with timetables exported from a generation software, so I'm not able to control the merging, unfortunately. :-/

 

[tjdickinson]

try this

If ActiveCell.MergeArea.Columns.Count > 1 And IsEmpty(cell.Value) = True Then

Thanks for your reply. When I try that in the code like this, nothing happens:

Sub UnMergeCell()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
    If ActiveCell.MergeArea.Columns.Count > 1 And IsEmpty(cell.Value) = True Then
            cell.Style = "Note"
      cell.UnMerge
    End If
    Next
End Sub

I tried changing 'Columns' to 'Rows' and setting it to = 1 , but that unmerged everything. Interestingly, when I set it to .Columns.Count = 1, it also unmerged everything--both rows and columns.

Did I insert it in the code incorrectly?

 

[Joe4]

I would recommend unmerging ALL cells from the get-go (regardless of whether or not they have values in them).
You can do that with a single line of code like this:

    ActiveSheet.Cells.UnMerge

and then work from there.

As was mentioned, leaving merged cells in there are probably going to cause you nothing but headaches for things like sorting and VBA, so it is strongly recommended that you eliminate them.
Very few serious programmers will use merged cells (and many won't even touch any questions on this forum dealing with them). They are that bad.
I understand that some programs export data that have them. The first thing I usually do is remove them all.

Also, note that you can easily achieve the same visual effect of horizontally merged cells by opting to use the "Center Across Selection" formatting option instead.
If gives the same visual effect without all the issues merged cells bring.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[tjdickinson]

I would recommend unmerging ALL cells from the get-go (regardless of whether or not they have values in them).
You can do that with a single line of code like this:

    ActiveSheet.Cells.UnMerge

and then work from there.

As was mentioned, leaving merged cells in there are probably going to cause you nothing but headaches for things like sorting and VBA, so it is strongly recommended that you eliminate them.

Also, you can easily achieve the same visual effect of horizontally merged cells by opting to use the "Center Across Selection" formatting option instead.
If gives the same visual effect without all the issues merged cells bring.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Very few serious programmers will use merged cells (and many won't even touch any questions on this forum dealing with them). They are that bad...

Thanks for the tip, and I'll certainly keep it in mind for other projects.

Unfortunately, I'm dealing with a file exported from a timetable generator, so I'm not able to control the merged cells.

I'm also not able to unmerge everything, because the vertically merged cells indicate a course spanning two hours. When the cells are unmerged, it makes it look like it's a one-hour course. Since the timetable for each individual is different, I can't code something that will re-merge (or 'Center Across Selection') those blocks. (As far as I know, but I'm still relatively novice when it comes to VBA.)

 

[Joe4]

I'm also not able to unmerge everything, because the vertically merged cells indicate a course spanning two hours. When the cells are unmerged, it makes it look like it's a one-hour course. Since the timetable for each individual is different, I can't code something that will re-merge (or 'Center Across Selection') those blocks. (As far as I know, but I'm still relatively novice when it comes to VBA.)

OK, I think I see your dilemma.

Take a look at this question here and the first reply. It has VBA code that will replace each merged cell with the value (so then inyour example, it should still show across two hours instead of one).

Unmerging excel rows, and duplicate data

I have been given a fairly large database stored in Microsoft Excel, which I have to try convert into something useful. However, one of the problems that I am encountering is that some of the data is

stackoverflow.com

Will something like that work for you?

 

[steve the fish]

Try something like:

For Each c In Selection
    If c.MergeArea.Columns.Count > 1 Then
        If c.MergeArea.Cells(1) = "" Then
            c.MergeArea.UnMerge
        End If
    End If
Next

 

[tjdickinson]

Try something like:

For Each c In Selection     ' looks at each cell in the selected area
    If c.MergeArea.Columns.Count > 1 Then     ' counts the number of columns in the merge area of the selected cell, and compares it to 1
        If c.MergeArea.Cells(1) = "" Then     ' if the column count is greater than 1, then compares the contents of the cell to an empty set
            c.MergeArea.UnMerge     ' if the cell is empty, unmerges the cell
        End If
    End If
Next

Amazing, that did the trick. I had to add Range ("A1:F15").Select at the start, but then it worked perfectly. Thanks!

I'm still quite novice when it comes to VBA, so I've commented in the code above what I think is going on...am I correct?