How to transform xls file with merged columns to csv?

user_12972073 0
Viewed 856

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

xls transform csv excel file
1 Answers

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.
user_27535010
Related