Using Excel Created files from other software with merged cells?

[Frank Shepard]

I use some engineering software that provides a download in excel.
It merges rows and columns to create a neat look but this makes it hard to cut and paste or do other manipulations.
I would like to undo these mergers and have a clean file?
See the attached sample.
Thanks much

 

[David Evans]

I use some engineering software that provides a download in excel.
It merges rows and columns to create a neat look but this makes it hard to cut and paste or do other manipulations.
I would like to undo these mergers and have a clean file?
See the attached sample.
Thanks much

Here's some code I've used in the past to "clean up" inherited workbooks - I hasten to add that it is not my original work and I've left the original attribution remarks in there .... it works on your sheet as I tried it. Install a module, copy it in there and run the macro Clear Merged.

When you've done that you will need to reset the column widths as they are completely out of whack, but that's easily done .. Also, some of your columns headers get shifted a little, but it's not incomprehensible ...

And you'll need to save the file as an".xlsm" to retain the VBA code .....

Option Explicit
'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 04/03/2007 08:48
' Author    : Roy Cox (royUK)
' Website  : www.excel-it.com for more examples
' Purpose  :  remove all merged cells from workbook
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'            projects but please leave this header intact.
'---------------------------------------------------------------------------------------



Public Sub ClearMerged()
    Dim oWs    As Excel.Worksheet
    Dim rCL    As Excel.Range
    Dim uRng  As Excel.Range
    Dim MyAddr As String
    Dim r      As Long
    Dim c      As Long
    Dim LastRow As Long
    Dim LastColumn As Long
On Error Resume Next
  
    Application.ScreenUpdating = False
  
    For Each oWs In ActiveWorkbook.Worksheets
    oWs.Select
        Set uRng = oWs.UsedRange
        LastRow = uRng.Rows(uRng.Rows.Count).Row
        LastColumn = uRng.Columns(uRng.Columns.Count).Column
      
            ' Find the merged cells
            For r = 1 To LastRow
                For c = 1 To LastColumn
                    Cells(r, c).Select
                    MyAddr = Selection.Address
                    If Len(WorksheetFunction.Substitute(MyAddr, ":", "")) <> Len(MyAddr) Then
                        With Range(MyAddr)
                            .Select
                            .MergeCells = False
                        End With
                    End If
                Next c
            Next r
          
        Next oWs
      
        Set uRng = Nothing

        On Error GoTo 0
        Application.ScreenUpdating = True

    End Sub