Unmerging and populating merged cells by column

[Bad at VBA]

This group is the best thing since sliced bread. The people that help
out here are the best, thank you!

With help from this group I've been able to copy and paste data across
multiple worksheets into a single sheet. This is huge progress for me,
and I now have one hurdle left.

Some of the cells in my combined data worksheet are merged. However,
my users are going to want to create pivot tables from the data, and
the merged cells affect the results.

So what I *think* I want to do (and someone may have a better idea) is
to:

1. Go column by column through the worksheet.
2. As it goes down a column, identify where there's a merged cell.
3. Determine how many rows it spans (as I'll need it for a later step)
4. Copy the data in the merged cell
5. Unmerge that cell.
6. Paste the original content of the merged cell into each now
unmerged row.
7. Continue down the column to the next merged cell (if any).
8. Move to the next column.

Is there a way to identify a merged cell vs. a non-merged cell? I
don't want to touch any un-merged cells.

Does this seem like a reasonable approach?

Thanks!

 

[Dave Peterson]

It sounds like you only merge cells a column at a time.

What happens if you find a range that's merged across multiple columns--like
A2:X2 or A2:X99?

Do all the cells get the same value?

If yes, then maybe...

Option Explicit
Sub testme()
Dim myCell As Range
Dim wks As Worksheet
Dim myVal As Variant
Dim HowManyRows As Long
Dim HowManyCols As Long

Set wks = Worksheets("Sheet1")

For Each myCell In wks.UsedRange.Cells
If myCell.MergeArea.Cells.Count > 1 Then
HowManyRows = myCell.MergeArea.Rows.Count
HowManyCols = myCell.MergeArea.Columns.Count
myVal = myCell.Value2
myCell.UnMerge
myCell.Resize(HowManyRows, HowManyCols).Value2 = myVal
End If
Next myCell
End Sub

Try it against a test copy of your worksheet--or close without saving if you
find a mistake.

 

[Bad at VBA]

Hi Dave,

Due to the way the data is generated, it can't be merged across
columns, only down rows. But in my quick test just now, it looks like
it worked exactly as I need it to. You ROCK!!!

 

[LumpyG]

Thanks

This worked perfectly for me as well. Thanks for the code.