Unmerging Cells and fill with value

[gldurand]

Hi Team

I have a spreadsheet that is has merged cells in columns.

I want to be able to unmerge the columns and fill the cells with the value of the merged cells.

ex:
I have a VALUE1 in merged cells A1:A15, i want to have that value in each cell A1 - A2 - A3 etc to A15,
I have a VALUE2 in merged cells A16:A20, i want to have that VALUE2 in each cell A16 - A17 etc to A20

It is never consistent, so probably VBA code to unmerge all cell in that column, and copy until a new value is found, then REPEAT.

Can anyone assist? I have several files in this situation and over 10000 rows of data

Thanks

 

[patel45]

please upload a sample file, with current column and desired result, on a sharing site, then paste the link here

 

[p45cal]

This will unmerge merged cells which consist of a single column only, acting on all columns of the active sheet. So if a merged area consists of cells from more than one column,it'll leave it alone.

Sub unmerge()
For Each cll In ActiveSheet.UsedRange.Cells
  If cll.MergeCells Then
    If cll.MergeArea.Columns.Count = 1 Then
      With cll.MergeArea
        .MergeCells = False
        .Value = cll.Value
      End With
    End If
  End If
Next cll
End Sub

It could be speeded up, so if time of execution is a problem, post again.

 

[gldurand]

Thanks so much P45CAL, it works like a charm, this savedme a huge amount of manual work.

All the best