VBA: Unmerge cells in rows

[FryGirl]

I know merged cells are bad, but the person I'm helping already has merged cells within their spreadsheet. Two questions...

1) I know thru VBA code how to unmerge a cell, but is there any way to count the number of cells that are merged and then copy the text to all of those cells. So example, if the first merged cell starts in R25, but goes thru x25. That's 7 cells. Can the VBA code determine there are 7 cells, copy the contents of R25 and then Resize by 7 and paste the results?

2) This is somewhat related. If the merged cells are white, can we skip that section and keep moving from left to right? Of course, once we hit the last column in the row, then we would start with the next row, row 26. All this would start with column L.

Thanks,

 

[Z51]

You can determine the number of cells in a merged area using something like:

Range("L1").MergeArea.Cells.Count

If L1:R1 is merged, the result will be 7. You can then unmerge that region, and copy the value across those cells.

Sub FryGirl()
Dim i as Long

i = Range("L1").MergeArea.Cells.Count
Range("L1").UnMerge
Range("L1").Resize(1, i).Value = Range("L1").Value
End Sub

To determine if a cell is merged, you can test using MergeCells, which returns True or False:

Range("L1").MergeCells

 

[FryGirl]

This is perfect, thanks.