Vertical and Horizontal Demerge (MS Excel)

If you have worked in spreadsheets, undoubtedly you will come across data tables where the column and row headers are merged. However stylistic and visually appealing these merged cells are, they pose issues when you want to run a pivot table on the data or create lookup tables to clean and transform the data. If your purpose is to pre-condition data for export into R or Python for analysis and visualization, these operations will also be quite handy for your toolkit.

Throughout my early years of working in MS Excel, I felt this pain daily. In response, I created a solutions to expedite my cleaning and processing efforts. This menial task took up time that could be used more effectively in other areas, like analysis of the data.

Each of the macros below work to demerge the merged cells and distribute the values across each of the merged cells affected. Arguably you could make the macros function along each vertical and horizontal axis in one macro, but i have not found that to be necessary and prefer the distinction between the two axis operations.

In this article we will go over demerging those cells in Microsoft Excel using VBA Macros. If you have never worked in Visual Basic for Applications (VBA) before, this may seem more complicated but continue to follow along.

VBA can be a very handy tool to automate tasks and process data that would normally consume your time, a valuable and scarce resource.

Access VBA from MS Excel

To open up VBA from the MS Excel application, press Alt+F11. This will open up a new window application. Alternatively, you can go to the “Developer” tab on the top menu, then select the “Visual Basic” button on the far left of the ribbon.

• 

• 

Column Demerge

In the first macro we will look at the Horizontal_Demerge macro so you can understand what is happening and why.

First we define our variables then assign those variables to their appropriate objects.

Dim ParentWB As Workbook
Dim CurrentWS As Worksheet
Dim PrimRange As Range

' 
Set ParentWB = ActiveWorkbook
Set CurrentWS = ParentWB.ActiveSheet

Set PrimRange = Application.InputBox(Prompt:="Demerge Columns", Title:="Specify Range to Demerge", Type:=8)

When the code runs, the last line in the code above will produce the prompt featured in the image below.

Next, the process prompts the user to select the range they would like to demerge. Once the user selects the desired range and clicks “Ok”, the process begins to execute.

ProcessRange = PrimRange.Address
Range(ProcessRange).UnMerge

The process begins by deriving the address range of the selected range. In the code above, the range address is assigned to the ProcessRange variable. It performs the native UnMerge operation over the selected range. When a range of merged cells UnMerge, the left most cell retains the value, leaving the rest empty.

For Each i In Range(ProcessRange)
    i_row = i.Row
    i_col = i.Column
    i_address = Cells(i_row, i_col).Address
    
    If i = "" Then
        Range(i_address).Value = Range(i_address).Offset(0, -1).Value
    End If
Next

The for-loop then processes each cell in the range. If the cell is blank or has no value, it takes the value of the preceding column (Offset(0,-1)) while holding the row reference static.

Once the completion message box pops up, you can then validate the process worked as expected.

Row Demerging

The only major difference between vertical (Row) and horizontal (Column) is which direction the values are derived. In the Vertical_Demerge macro, the values come from above, whereas the Horizontal_Demerge macro gets values from the left.

This routine can be used to process multiple columns of row headers.

The code difference between the two operations is the Range(i_address).Offset(-1, 0).Value section. The offset function takes the value from the preceding row (-1) while holding the column reference static, whereas the offset function in the Horizonal_Demerge routine (Offset(0,-1)) holds the row static and takes the preceding column.

Range(ProcessRange).UnMerge
For Each i In Range(ProcessRange)
    i_row = i.Row
    i_col = i.Column
    i_address = Cells(i_row, i_col).Address
    
    If i = "" Then
        Range(i_address).Value = Range(i_address).Offset(-1, 0).Value
    End If
Next

• 

• 

When we run the macro, we get the prompt for us to select our desired range of rows to demerge.

• 

• 

Select the desired range of Row Headers you would like to Demerge.

• 

• 

After you click “Ok”, the process runs and presents the message box indicating the process is complete.

• 

• 

Finally, we validate the process we designed did what we expected it to do.

• 

• 

Conclusion

While the overall code could be improved, it works and functions as I want and it did not take long to produce. If this were going to be part of a professional deliverable, I would likely spend more time building error catches, and polishing up the presentation. At the end of the day, it gets the job done and saves me time. Refine as desired.

What’s Next?

In a future post, I will document a guide to create your own ribbon bar in MS Excel and put your VBA macros in that ribbon bar so you can develop your own toolkit of features, accessible at the push of a button.