VBA Macro to unmerge empty merged cells

tjdickinson

Board Regular
Joined
Jun 26, 2021
Messages
61
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

Board Regular
Joined
Jun 26, 2021
Messages
61
Office Version
  1. 365
Platform
  1. Windows
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).

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.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,535
Office Version
  1. 365
Platform
  1. Windows
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).
 
Upvote 0

Forum statistics

Threads
1,186,628
Messages
5,958,883
Members
438,381
Latest member
rcwilk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top