macro to unmerge cells

[Papa Jonah]

I have another application that I export data into Excel from.
Unfortunately, these exports are hundreds of rows and about 20 columns.
Scattered throughout are columns that may have anywhere from 2-15 rows merged
into a cell. When that happens, most (usually not all) of the other columns
have the same merged rows. Since you can't sort when any of the cells are
merged, I need a macro that will help me to identify and unmerge these cells.
What I was thinking was identify the currentregion from cell A5 and have
some sort of loop that will have the macro check every cell in the region and
unmerge it if necessary. Eventhough I am not sure how to do that, it doesn't
sound like it would probably not be the most efficient approach anyway - open
to ideas.
TIA
Papa

 

[Dave Peterson]

If you're going to unmerge them, don't bother checking. Just do all the cells
you want.

Option Explicit
Sub testme()
With ActiveSheet
.Range("5:" & .Rows.Count).MergeCells = False
End With
End Sub

 

[Gary''s Student]

Sub divorce()
Cells.MergeCells = False
End Sub


Will un-merge ALL cells.

 

[Rick Rothstein \(MVP - VB\)]

I'm guessing that you can do away with the With statement since I don't
think the Rows.Count needs to be dotted... all sheets in the workbook should
have the same number of rows, so I don't think it should matter which one is
referenced to calculate the Rows.Count.

Sub TestMe()
ActiveSheet.Range("5:" & Rows.Count).MergeCells = False
End Sub

With that said, I'm guessing the OP suggested starting at Row 5 because that
is where the first merged cell is. Then, for his needs, I would think we can
just reference the UsedRange and "unmerge" it...

Sub TestMe()
ActiveSheet.UsedRange.MergeCells = False
End Sub

Rick

 

[Papa Jonah]

Oh Man - that is awesome! I should have asked the question months ago!
Thanks!

 

[Rick Rothstein \(MVP - VB\)]

But it looks like Gary''s Student came up with the best solution...

Rick

I'm guessing that you can do away with the With statement since I don't
think the Rows.Count needs to be dotted... all sheets in the workbook
should have the same number of rows, so I don't think it should matter
which one is referenced to calculate the Rows.Count.

Sub TestMe()
ActiveSheet.Range("5:" & Rows.Count).MergeCells = False
End Sub

With that said, I'm guessing the OP suggested starting at Row 5 because
that is where the first merged cell is. Then, for his needs, I would think
we can just reference the UsedRange and "unmerge" it...

Sub TestMe()
ActiveSheet.UsedRange.MergeCells = False
End Sub

Rick

 

[Dave Peterson]

I like qualifying my objects--including .rows.

And I guessed that there would be headers in rows 1-4 that should not be
unmerged.