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.

 

[tjdickinson]

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?

Thanks for your quick reply! I think this is a very good trick to learn and remember, and had a solution not been found, this might have been a suitable workaround.

Unfortunately, I am, indeed, trying to use macros and VBA for formatting and aesthetics, trying to make the timetable look its best before we distribute it to the teachers and students, so while duplicating the data would preserve the information, it would require either more code to identify duplicate cells and re-merge them, or me to do it manually. Though there is still a bit of case-by-case work I have to do, I'm trying to automate as much of it as possible.

The good news is that someone was able to provide a solution (the issue seems to be how the code was identifying the empty cells and restricting the unmerge action), but again, I thank you very much for the helpful information, which will certainly be useful as I come to understand VBA better.

 

[Joe4]

Thanks for your quick reply! I think this is a very good trick to learn and remember, and had a solution not been found, this might have been a suitable workaround.

Unfortunately, I am, indeed, trying to use macros and VBA for formatting and aesthetics, trying to make the timetable look its best before we distribute it to the teachers and students, so while duplicating the data would preserve the information, it would require either more code to identify duplicate cells and re-merge them, or me to do it manually. Though there is still a bit of case-by-case work I have to do, I'm trying to automate as much of it as possible.

The good news is that someone was able to provide a solution (the issue seems to be how the code was identifying the empty cells and restricting the unmerge action), but again, I thank you very much for the helpful information, which will certainly be useful as I come to understand VBA better.

Glad you were able to find a solution that works for you (and maybe picked up on some tips along the way).