How can I unmerge the merged cells in a Excel sheet quickly?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 said:
Pls help me :)

Tomorrow said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
Pls help me :)

Tomorrow said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Pls help me :)

:

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
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

Pls help me :)

:

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

:

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

Pls help me :)

:

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.
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
should of course read
...in the other 3 cells

Regards,
Peter

Tomorrow said:
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 said:
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 said:
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

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

:

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

Pls help me :)

:

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.
Click to expand...
Click to expand...
Click to expand...
 
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
(e-mail address removed).

Best Regards,
Tomorrow


Peter T said:
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
Click to expand...
should of course read
...in the other 3 cells

Regards,
Peter

Tomorrow said:
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 said:
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


:

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

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

:

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

Pls help me :)

:

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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
(e-mail address removed).

Best Regards,
Tomorrow


Peter T said:
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:
should of course read
...in the other 3 cells

Regards,
Peter

find
the you
want B2
can not
above
Click to expand...
snip<
Click to expand...
 
Back
Top