Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I unmerge the merged cells in a Excel sheet quickly?
I want a function like this:
to unmerge all merged cells in a Excel sheet and replaced all the merged cells value with the originally merged area value after unmerging action. I use the a cycle to fulfill it. But it's too too slowly if the Excel sheet data is a little big. The code (c# code) as below: for (int i=1; i<=Int32.Parse(ws.UsedRange.Rows.Count.ToString()) ;i++) { for (int j=1; j<=Int32.Parse(ws.UsedRange.Columns.Count.ToString ()); j++) { Excel.Range Range = (Excel.Range)ws.Cells[i,j]; if (bool.Parse(Range.MergeCells.ToString())) { int x = Int32.Parse(Range.MergeArea.Rows.Count.ToString()) ; int y = Int32.Parse(Range.MergeArea.Columns.Count.ToString ()); string TempValue = Range.Text.ToString(); Range.MergeArea.UnMerge(); for (int m = i; m<(i+x); m++) { for (int n = j; n<(j+y); n++) { Excel.Range TempRange = (Excel.Range)ws.Cells[m,n]; TempRange.set_Value( Type.Missing,TempValue); } } } } } Does Somebody have another idea to do it quickly?? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I unmerge the merged cells in a Excel sheet quickly?
Pls help me :)
"Tomorrow" wrote: I want a function like this: to unmerge all merged cells in a Excel sheet and replaced all the merged cells value with the originally merged area value after unmerging action. I use the a cycle to fulfill it. But it's too too slowly if the Excel sheet data is a little big. The code (c# code) as below: for (int i=1; i<=Int32.Parse(ws.UsedRange.Rows.Count.ToString()) ;i++) { for (int j=1; j<=Int32.Parse(ws.UsedRange.Columns.Count.ToString ()); j++) { Excel.Range Range = (Excel.Range)ws.Cells[i,j]; if (bool.Parse(Range.MergeCells.ToString())) { int x = Int32.Parse(Range.MergeArea.Rows.Count.ToString()) ; int y = Int32.Parse(Range.MergeArea.Columns.Count.ToString ()); string TempValue = Range.Text.ToString(); Range.MergeArea.UnMerge(); for (int m = i; m<(i+x); m++) { for (int n = j; n<(j+y); n++) { Excel.Range TempRange = (Excel.Range)ws.Cells[m,n]; TempRange.set_Value( Type.Missing,TempValue); } } } } } Does Somebody have another idea to do it quickly?? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I unmerge the merged cells in a Excel sheet quickly?
Why can't you just select all cells in the sheet (small square that
intersects headers), Format cells Alignment and untick the Merge cells box (may need to click it twice). Record a macro for the code. Regards, Peter "Tomorrow" wrote in message ... Pls help me :) "Tomorrow" wrote: I want a function like this: to unmerge all merged cells in a Excel sheet and replaced all the merged cells value with the originally merged area value after unmerging action. I use the a cycle to fulfill it. But it's too too slowly if the Excel sheet data is a little big. The code (c# code) as below: for (int i=1; i<=Int32.Parse(ws.UsedRange.Rows.Count.ToString()) ;i++) { for (int j=1; j<=Int32.Parse(ws.UsedRange.Columns.Count.ToString ()); j++) { Excel.Range Range = (Excel.Range)ws.Cells[i,j]; if (bool.Parse(Range.MergeCells.ToString())) { int x = Int32.Parse(Range.MergeArea.Rows.Count.ToString()) ; int y = Int32.Parse(Range.MergeArea.Columns.Count.ToString ()); string TempValue = Range.Text.ToString(); Range.MergeArea.UnMerge(); for (int m = i; m<(i+x); m++) { for (int n = j; n<(j+y); n++) { Excel.Range TempRange = (Excel.Range)ws.Cells[m,n]; TempRange.set_Value( Type.Missing,TempValue); } } } } } Does Somebody have another idea to do it quickly?? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I unmerge the merged cells in a Excel sheet quickly?
Thanks, Peter:
I tried the method you told below, but I can't read the all merged areas data(not only one merged area). The Macro code as below: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/21/2004 by Tomorrow ' ' Range("A1:AU138").Select Range("AB120").Activate With Selection .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub Best regards, Tomorrow "Peter T" wrote: Why can't you just select all cells in the sheet (small square that intersects headers), Format cells Alignment and untick the Merge cells box (may need to click it twice). Record a macro for the code. Regards, Peter "Tomorrow" wrote in message ... Pls help me :) "Tomorrow" wrote: I want a function like this: to unmerge all merged cells in a Excel sheet and replaced all the merged cells value with the originally merged area value after unmerging action. I use the a cycle to fulfill it. But it's too too slowly if the Excel sheet data is a little big. The code (c# code) as below: for (int i=1; i<=Int32.Parse(ws.UsedRange.Rows.Count.ToString()) ;i++) { for (int j=1; j<=Int32.Parse(ws.UsedRange.Columns.Count.ToString ()); j++) { Excel.Range Range = (Excel.Range)ws.Cells[i,j]; if (bool.Parse(Range.MergeCells.ToString())) { int x = Int32.Parse(Range.MergeArea.Rows.Count.ToString()) ; int y = Int32.Parse(Range.MergeArea.Columns.Count.ToString ()); string TempValue = Range.Text.ToString(); Range.MergeArea.UnMerge(); for (int m = i; m<(i+x); m++) { for (int n = j; n<(j+y); n++) { Excel.Range TempRange = (Excel.Range)ws.Cells[m,n]; TempRange.set_Value( Type.Missing,TempValue); } } } } } Does Somebody have another idea to do it quickly?? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I unmerge the merged cells in a Excel sheet quickly?
I might have misunderstood your original question, are you saying you want
to do something like this: - First merged area is (say) B2:B5 and B2 contains value "X" (only B2 can contain data) - Unmerge B2:B5 and put "X" in the other 7 cells. - Do similar with all merged areas on the sheet Sub TestUnmerge() Dim r As Range, rMgArea As Range For Each r In ActiveSheet.UsedRange If r.MergeArea.Cells.Count 1 Then Set rMgArea = r.MergeArea r.UnMerge rMgArea.Value = r.Value End If Next End Sub This assumes by "value" you mean constants and not formulas. If not above would need to be changed. Regards, Peter "Tomorrow" wrote in message ... Thanks, Peter: I tried the method you told below, but I can't read the all merged areas data(not only one merged area). The Macro code as below: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/21/2004 by Tomorrow ' ' Range("A1:AU138").Select Range("AB120").Activate With Selection .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub Best regards, Tomorrow "Peter T" wrote: Why can't you just select all cells in the sheet (small square that intersects headers), Format cells Alignment and untick the Merge cells box (may need to click it twice). Record a macro for the code. Regards, Peter "Tomorrow" wrote in message ... Pls help me :) "Tomorrow" wrote: I want a function like this: to unmerge all merged cells in a Excel sheet and replaced all the merged cells value with the originally merged area value after unmerging action. I use the a cycle to fulfill it. But it's too too slowly if the Excel sheet data is a little big. The code (c# code) as below: for (int i=1; i<=Int32.Parse(ws.UsedRange.Rows.Count.ToString()) ;i++) { for (int j=1; j<=Int32.Parse(ws.UsedRange.Columns.Count.ToString ()); j++) { Excel.Range Range = (Excel.Range)ws.Cells[i,j]; if (bool.Parse(Range.MergeCells.ToString())) { int x = Int32.Parse(Range.MergeArea.Rows.Count.ToString()) ; int y = Int32.Parse(Range.MergeArea.Columns.Count.ToString ()); string TempValue = Range.Text.ToString(); Range.MergeArea.UnMerge(); for (int m = i; m<(i+x); m++) { for (int n = j; n<(j+y); n++) { Excel.Range TempRange = (Excel.Range)ws.Cells[m,n]; TempRange.set_Value( Type.Missing,TempValue); } } } } } Does Somebody have another idea to do it quickly?? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I unmerge the merged cells in a Excel sheet quickly?
Thaks, Peter:
Yeah, I want function just like that you said below. I had tried the method you teached, but it can't work. The error means that can't find the Range obeject with which use the foreach sentence. Did you work ok? Regards, Tomorrow "Peter T" wrote: I might have misunderstood your original question, are you saying you want to do something like this: - First merged area is (say) B2:B5 and B2 contains value "X" (only B2 can contain data) - Unmerge B2:B5 and put "X" in the other 7 cells. - Do similar with all merged areas on the sheet Sub TestUnmerge() Dim r As Range, rMgArea As Range For Each r In ActiveSheet.UsedRange If r.MergeArea.Cells.Count 1 Then Set rMgArea = r.MergeArea r.UnMerge rMgArea.Value = r.Value End If Next End Sub This assumes by "value" you mean constants and not formulas. If not above would need to be changed. Regards, Peter "Tomorrow" wrote in message ... Thanks, Peter: I tried the method you told below, but I can't read the all merged areas data(not only one merged area). The Macro code as below: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/21/2004 by Tomorrow ' ' Range("A1:AU138").Select Range("AB120").Activate With Selection .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub Best regards, Tomorrow "Peter T" wrote: Why can't you just select all cells in the sheet (small square that intersects headers), Format cells Alignment and untick the Merge cells box (may need to click it twice). Record a macro for the code. Regards, Peter "Tomorrow" wrote in message ... Pls help me :) "Tomorrow" wrote: I want a function like this: to unmerge all merged cells in a Excel sheet and replaced all the merged cells value with the originally merged area value after unmerging action. I use the a cycle to fulfill it. But it's too too slowly if the Excel sheet data is a little big. The code (c# code) as below: for (int i=1; i<=Int32.Parse(ws.UsedRange.Rows.Count.ToString()) ;i++) { for (int j=1; j<=Int32.Parse(ws.UsedRange.Columns.Count.ToString ()); j++) { Excel.Range Range = (Excel.Range)ws.Cells[i,j]; if (bool.Parse(Range.MergeCells.ToString())) { int x = Int32.Parse(Range.MergeArea.Rows.Count.ToString()) ; int y = Int32.Parse(Range.MergeArea.Columns.Count.ToString ()); string TempValue = Range.Text.ToString(); Range.MergeArea.UnMerge(); for (int m = i; m<(i+x); m++) { for (int n = j; n<(j+y); n++) { Excel.Range TempRange = (Excel.Range)ws.Cells[m,n]; TempRange.set_Value( Type.Missing,TempValue); } } } } } Does Somebody have another idea to do it quickly?? Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I unmerge the merged cells in a Excel sheet quickly?
Sorry, I had tried your code. It's ok.
May be C# code must have some difference on it. I'll go on trying it. thanks! "Tomorrow" wrote: Thaks, Peter: Yeah, I want function just like that you said below. I had tried the method you teached, but it can't work. The error means that can't find the Range obeject with which use the foreach sentence. Did you work ok? Regards, Tomorrow "Peter T" wrote: I might have misunderstood your original question, are you saying you want to do something like this: - First merged area is (say) B2:B5 and B2 contains value "X" (only B2 can contain data) - Unmerge B2:B5 and put "X" in the other 7 cells. - Do similar with all merged areas on the sheet Sub TestUnmerge() Dim r As Range, rMgArea As Range For Each r In ActiveSheet.UsedRange If r.MergeArea.Cells.Count 1 Then Set rMgArea = r.MergeArea r.UnMerge rMgArea.Value = r.Value End If Next End Sub This assumes by "value" you mean constants and not formulas. If not above would need to be changed. Regards, Peter "Tomorrow" wrote in message ... Thanks, Peter: I tried the method you told below, but I can't read the all merged areas data(not only one merged area). The Macro code as below: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/21/2004 by Tomorrow ' ' Range("A1:AU138").Select Range("AB120").Activate With Selection .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub Best regards, Tomorrow "Peter T" wrote: Why can't you just select all cells in the sheet (small square that intersects headers), Format cells Alignment and untick the Merge cells box (may need to click it twice). Record a macro for the code. Regards, Peter "Tomorrow" wrote in message ... Pls help me :) "Tomorrow" wrote: I want a function like this: to unmerge all merged cells in a Excel sheet and replaced all the merged cells value with the originally merged area value after unmerging action. I use the a cycle to fulfill it. But it's too too slowly if the Excel sheet data is a little big. The code (c# code) as below: for (int i=1; i<=Int32.Parse(ws.UsedRange.Rows.Count.ToString()) ;i++) { for (int j=1; j<=Int32.Parse(ws.UsedRange.Columns.Count.ToString ()); j++) { Excel.Range Range = (Excel.Range)ws.Cells[i,j]; if (bool.Parse(Range.MergeCells.ToString())) { int x = Int32.Parse(Range.MergeArea.Rows.Count.ToString()) ; int y = Int32.Parse(Range.MergeArea.Columns.Count.ToString ()); string TempValue = Range.Text.ToString(); Range.MergeArea.UnMerge(); for (int m = i; m<(i+x); m++) { for (int n = j; n<(j+y); n++) { Excel.Range TempRange = (Excel.Range)ws.Cells[m,n]; TempRange.set_Value( Type.Missing,TempValue); } } } } } Does Somebody have another idea to do it quickly?? Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I unmerge the merged cells in a Excel sheet quickly?
Glad it works, in VBA at least.
When you have it working in C#, could you post the code - I will learn something! Typo in my previous comments: - Unmerge B2:B5 and put "X" in the other 7 cells should of course read ...in the other 3 cells Regards, Peter "Tomorrow" wrote in message ... Sorry, I had tried your code. It's ok. May be C# code must have some difference on it. I'll go on trying it. thanks! "Tomorrow" wrote: Thaks, Peter: Yeah, I want function just like that you said below. I had tried the method you teached, but it can't work. The error means that can't find the Range obeject with which use the foreach sentence. Did you work ok? Regards, Tomorrow "Peter T" wrote: I might have misunderstood your original question, are you saying you want to do something like this: - First merged area is (say) B2:B5 and B2 contains value "X" (only B2 can contain data) - Unmerge B2:B5 and put "X" in the other 7 cells. - Do similar with all merged areas on the sheet Sub TestUnmerge() Dim r As Range, rMgArea As Range For Each r In ActiveSheet.UsedRange If r.MergeArea.Cells.Count 1 Then Set rMgArea = r.MergeArea r.UnMerge rMgArea.Value = r.Value End If Next End Sub This assumes by "value" you mean constants and not formulas. If not above would need to be changed. Regards, Peter "Tomorrow" wrote in message ... Thanks, Peter: I tried the method you told below, but I can't read the all merged areas data(not only one merged area). The Macro code as below: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/21/2004 by Tomorrow ' ' Range("A1:AU138").Select Range("AB120").Activate With Selection .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub Best regards, Tomorrow "Peter T" wrote: Why can't you just select all cells in the sheet (small square that intersects headers), Format cells Alignment and untick the Merge cells box (may need to click it twice). Record a macro for the code. Regards, Peter "Tomorrow" wrote in message ... Pls help me :) "Tomorrow" wrote: I want a function like this: to unmerge all merged cells in a Excel sheet and replaced all the merged cells value with the originally merged area value after unmerging action. I use the a cycle to fulfill it. But it's too too slowly if the Excel sheet data is a little big. The code (c# code) as below: for (int i=1; i<=Int32.Parse(ws.UsedRange.Rows.Count.ToString()) ;i++) { for (int j=1; j<=Int32.Parse(ws.UsedRange.Columns.Count.ToString ()); j++) { Excel.Range Range = (Excel.Range)ws.Cells[i,j]; if (bool.Parse(Range.MergeCells.ToString())) { int x = Int32.Parse(Range.MergeArea.Rows.Count.ToString()) ; int y = Int32.Parse(Range.MergeArea.Columns.Count.ToString ()); string TempValue = Range.Text.ToString(); Range.MergeArea.UnMerge(); for (int m = i; m<(i+x); m++) { for (int n = j; n<(j+y); n++) { Excel.Range TempRange = (Excel.Range)ws.Cells[m,n]; TempRange.set_Value( Type.Missing,TempValue); } } } } } Does Somebody have another idea to do it quickly?? Thanks. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I unmerge the merged cells in a Excel sheet quickly?
Thanks for posting the C# code. Your comment that this is slower than VBA is
interesting, I had always been under the impression that VBA is slower. I tested "For 1 to .UsedRange.Count" vs "For Each" with data in 1500 merged areas in a UR of 10,000 cells. Both slow but virtually same time. So I don't think the VBA vs C# speed difference is related to "For Each". However I found this about twice as fast: Sub TestUnmerge3() Dim i As Long, n As Long ReDim ay(1, 0) With ActiveSheet.UsedRange For i = 1 To .Count If .Cells(i).MergeArea.Count 1 Then If .Cells(i) < "" Then ' And .Cells(i).HasFormula = False '? n = n + 1 ReDim Preserve ay(1, n) ay(0, n) = .Cells(i).MergeArea.Address ay(1, n) = .Cells(i).Value End If End If Next ..UnMerge End With For i = 1 To n Range(ay(0, i)).Value = ay(1, i) Next End Sub Even with all the array stuff, taking '.UnMerge' [each single mergearea] out of the loop seems to give a dramatic speed improvement. Regards, Peter "Tomorrow" wrote in message ... Dear Peter: Because Range is a class in C#, so I can't use the Foreach statement as in VBA code. Finally, I use a replacement method to do the same thing. Though it is lower than your method. The C# code as below: for (int i=1; i<=Int32.Parse(ws.UsedRange.Rows.Count.ToString()) ;i++) { for (int j=1; j<=Int32.Parse(ws.UsedRange.Columns.Count.ToString ()); j++) { Excel.Range Range =(Excel.Range) ws.UsedRange[i,j]; if (bool.Parse(Range.MergeCells.ToString())) { Excel.Range rMergeArea = (Excel.Range) Range.MergeArea; Range.MergeArea.UnMerge(); rMergeArea.Value2 = Range.Value2; } } } In a word, thank you very very much for your reply and guide for me. And I'm very pleasure to make a friend with you. My mail is . Best Regards, Tomorrow "Peter T" wrote: Glad it works, in VBA at least. When you have it working in C#, could you post the code - I will learn something! Typo in my previous comments: - Unmerge B2:B5 and put "X" in the other 7 cells should of course read ...in the other 3 cells Regards, Peter "Tomorrow" wrote in message ... Sorry, I had tried your code. It's ok. May be C# code must have some difference on it. I'll go on trying it. thanks! "Tomorrow" wrote: Thaks, Peter: Yeah, I want function just like that you said below. I had tried the method you teached, but it can't work. The error means that can't find the Range obeject with which use the foreach sentence. Did you work ok? Regards, Tomorrow "Peter T" wrote: I might have misunderstood your original question, are you saying you want to do something like this: - First merged area is (say) B2:B5 and B2 contains value "X" (only B2 can contain data) - Unmerge B2:B5 and put "X" in the other 7 cells. - Do similar with all merged areas on the sheet Sub TestUnmerge() Dim r As Range, rMgArea As Range For Each r In ActiveSheet.UsedRange If r.MergeArea.Cells.Count 1 Then Set rMgArea = r.MergeArea r.UnMerge rMgArea.Value = r.Value End If Next End Sub This assumes by "value" you mean constants and not formulas. If not above would need to be changed. Regards, Peter snip< |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I unmerge an entire spread sheet in excel? | Excel Worksheet Functions | |||
sort spreadsheet, "merged cells" comes up. Find cells? Unmerge ? | Excel Discussion (Misc queries) | |||
Cannot merge or unmerge cells in Excel | Excel Discussion (Misc queries) | |||
UNMERGE two cells/rows in Excel | Excel Discussion (Misc queries) | |||
Can't unmerge merged cells programatically | Excel Programming |