Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to unmerge cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to unmerge cells
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 Papa Jonah wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to unmerge cells
Sub divorce()
Cells.MergeCells = False End Sub Will un-merge ALL cells. -- Gary''s Student - gsnu200791 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to unmerge cells
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" wrote in message ... 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 Papa Jonah wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to unmerge cells
Oh Man - that is awesome! I should have asked the question months ago!
Thanks! "Dave Peterson" wrote: 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 Papa Jonah wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to unmerge cells
But it looks like Gary''s Student came up with the best solution...
Rick "Rick Rothstein (MVP - VB)" wrote in message ... 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" wrote in message ... 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 Papa Jonah wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to unmerge cells
I like qualifying my objects--including .rows.
And I guessed that there would be headers in rows 1-4 that should not be unmerged. "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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 Papa Jonah wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unmerge cells | Excel Discussion (Misc queries) | |||
MACRO - Unmerge cells and delete blank columns, leaving data colum | Excel Programming | |||
How do you unmerge cells | Excel Discussion (Misc queries) | |||
How do I get macro to unmerge cells that have been previously merg | New Users to Excel | |||
How do you unmerge cells? | Excel Discussion (Misc queries) |