excelcsvfiletransformxls

How to transform xls file with merged columns to csv?


I'm trying to transform some .xls files to .csv, and one of the files has some merged columns. When I do the transformation, only one value is returned. This image will explain the situation: Problem in image


Solution

  • First you should unmerge cells in your .xls file. The given code will unmerge cells properly.

    before - merged ==>> after - unmerged

    1. Open your sheet.
    2. Alt + F11
    3. Insert => Module
    4. Paste this code:
    Sub activesheet_unmerge()
      Dim c As Range
      Dim c2 As Range
      Dim rMergeArea As Range
      Dim vMergeValue As Variant
      For Each c In ActiveSheet.UsedRange
        If c.MergeCells Then
          Set rMergeArea = c.MergeArea
          vMergeValue = c.Value
          rMergeArea.unmerge
          For Each c2 In rMergeArea
            c2.Value = vMergeValue
          Next
        End If
      Next
    End Sub
    
    1. Put the mouse pointer somewhere in the middle of this code and hit F5 to run the code.