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:
First you should unmerge cells in your .xls file. The given code will unmerge cells properly.
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