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?
 
JofnnConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Rory ArchibaldConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.