Code for unmerging any cells on all the worksheets in a workbook excel 2007

I have a problem, a system that we get our data from sends me a email with data. when I try to code it to copy and paste the data the formate is messed up because the data that was sent to me in the email (which is a excel file) has merged some of the fields. I would like to run something that will unmerge all the cells in the workbook before the code will do the copy and paste steps. Please help...Thank you!
cdb424ttmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JofnnCommented:
Paste the following into your Visual Basic Editor (Alt + F11) under a new module

Sub UnmergeAll()
For Each ws In ActiveWorkbook.Worksheets

    If ws.Name <> "notarealname" Then
        ws.Activate
        Cells.Select
        Selection.UnMerge
    Else
    End If
Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JofnnCommented:
That will look for all worksheets that are not called "notarealname", select all cells and then tell them to unmerge.  Just make sure that your sheets/cells etc are not protected :)

0
JofnnCommented:
Alternatively, to save it selecting where your mouse has been ... you could amend:

cells.select

Open in new window

with
Range("A:CC").select

Open in new window


replacing the range with whatever you want!
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Rory ArchibaldCommented:
No need to select anything:
Sub UnmergeAll()
For Each ws In ActiveWorkbook.Worksheets

    If ws.Name <> "notarealname" Then
        ws.usedrange.UnMerge
    End If
Next
End Sub

Open in new window

0
JofnnCommented:
Cheers Rorya... completely forgot about "usedrange"!!
0
Rob HensonFinance AnalystCommented:
I am reading/understanding the problem in a slightly different way.

The above comments are giving guidance on how to unmerge cells that are merged together. I am reading it as multiple sets of data have been merged into one cell so need separating out to multiple cells using the "Text to Columns" function or something similar.

Thanks
Rob H
0
Rory ArchibaldCommented:
Since my code was just a tweak of Jofnn's original code, I think the points should not have gone to me. I suggest you click the request attention link at the top and ask for the question to be reopened so you can reaward them.
Regards,
Rory
0
JofnnCommented:
Thanks rorya, appreciated!
0
South ModModeratorCommented:
Allocating points in a more equitable manner.

SouthMod
Community Support Moderator
0
cdb424ttmAuthor Commented:
That sounds fair to me. Thanks to both of you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.