I am looking for a simple macro that will umerge a cell and copy the value
in the original merged cell into all the cells created as a result of the
merge. Any suggestions?

Thanx,
--
Anil Gupte

Select the merged cell, then run this macro:

Sub UnMergeCopy()
Selection.MergeCells = False
On Error Resume Next
Selection.FillRight
Selection.FillDown
End Sub

Regards,
Stefi


€žAnil Gupte€� ezt Ã*rta:

I am looking for a simple macro that will umerge a cell and copy the value
in the original merged cell into all the cells created as a result of the
merge. Any suggestions?

Thanx,
--
Anil Gupte

Fantastic! Too Cool!

Thanx,
--
Anil Gupte
"Stefi" wrote in message
...
Select the merged cell, then run this macro:

Sub UnMergeCopy()
Selection.MergeCells = False
On Error Resume Next
Selection.FillRight
Selection.FillDown
End Sub

Regards,
Stefi


"Anil Gupte" ezt írta:

I am looking for a simple macro that will umerge a cell and copy the
value
in the original merged cell into all the cells created as a result of the
merge. Any suggestions?

Thanx,
--
Anil Gupte

OK, I celebrated too soon :-(

It works great with multi-row cells, but with cells merged from a single row
and multiple columns, it copies text from the row above the row I run the
macro in.

Also, can I assign a key in the code itself?

Finally, without going through conniptions with assigning security can I set
it so that it runs the macro in any sheet I bring up?

Thanx,
--
Anil Gupte

www.keeninc.net
www.icinema.com
k.e.e.n., inc.
Milwaukee, WI, USA
+1-414/431-8775
Pune, MH, India
+91-20/30223100
"Stefi" wrote in message
...
Select the merged cell, then run this macro:

Sub UnMergeCopy()
Selection.MergeCells = False
On Error Resume Next
Selection.FillRight
Selection.FillDown
End Sub

Regards,
Stefi


"Anil Gupte" ezt írta:

I am looking for a simple macro that will umerge a cell and copy the
value
in the original merged cell into all the cells created as a result of the
merge. Any suggestions?

Thanx,
--
Anil Gupte

I did some quick learning and came up with this:
**************
Sub Unmerge()
Dim Rng As Range
Dim cell As Range
Dim v As String
v = Selection.Value
Selection.MergeCells = False
On Error Resume Next
Set Rng = Selection
For Each cell In Rng
cell.Value = v
Next cell
' Selection.FillRight
' Selection.FillDown
End Sub

**************
Only problem of course is that
v = Selection.Value

is not valid. I tried

v = Selection.item(0).value

and several other variations, but those did not work. How do I get the
original value?


--
Anil Gupte
"Stefi" wrote in message
...
Select the merged cell, then run this macro:

Sub UnMergeCopy()
Selection.MergeCells = False
On Error Resume Next
Selection.FillRight
Selection.FillDown
End Sub

Regards,
Stefi


"Anil Gupte" ezt írta:

I am looking for a simple macro that will umerge a cell and copy the
value
in the original merged cell into all the cells created as a result of the
merge. Any suggestions?

Thanx,
--
Anil Gupte

This is a better tested version:
Sub UnMergeCopy()
Selection.MergeCells = False
On Error Resume Next
If Selection.Columns.Count 1 Then
Selection.FillRight
End If
If Selection.Rows.Count 1 Then
Selection.FillDown
End If
End Sub


Also, can I assign a key in the code itself?

If you mean assigning a hot key: Tools/Macro (or Alt-F8) / Others
(bottom-right button

Finally, without going through conniptions with assigning security can I set
it so that it runs the macro in any sheet I bring up?

This macro does run in any sheet!

Regards,
Stefi

I hope my former reply will be sufficient, but in this example
v = ActiveCell.Value
is the right line!

Regards,
Stefi

€žAnil Gupte€� ezt Ã*rta:

I did some quick learning and came up with this:
**************
Sub Unmerge()
Dim Rng As Range
Dim cell As Range
Dim v As String
v = Selection.Value
Selection.MergeCells = False
On Error Resume Next
Set Rng = Selection
For Each cell In Rng
cell.Value = v
Next cell
' Selection.FillRight
' Selection.FillDown
End Sub

**************
Only problem of course is that
v = Selection.Value

is not valid. I tried

v = Selection.item(0).value

and several other variations, but those did not work. How do I get the
original value?


--
Anil Gupte
"Stefi" wrote in message
...
Select the merged cell, then run this macro:

Sub UnMergeCopy()
Selection.MergeCells = False
On Error Resume Next
Selection.FillRight
Selection.FillDown
End Sub

Regards,
Stefi


"Anil Gupte" ezt Ã*rta:

I am looking for a simple macro that will umerge a cell and copy the
value
in the original merged cell into all the cells created as a result of the
merge. Any suggestions?

Thanx,
--
Anil Gupte

That is working. Appreciate it.

What I meant about the Hot-Key selection is that it shoul dbe in th code
itself. I can just tell the users to load the sheet with the macro and
enter a key combination. You will be surprised how much work is invloved in
getting them to understand Tools-Macro-Choose the Macro-Options
blah-blah.

Anyway, thanx again.

--
Anil Gupte

www.keeninc.net
www.icinema.com
k.e.e.n., inc.
Milwaukee, WI, USA
+1-414/431-8775
Pune, MH, India
+91-20/30223100
"Stefi" wrote in message
...
This is a better tested version:
Sub UnMergeCopy()
Selection.MergeCells = False
On Error Resume Next
If Selection.Columns.Count 1 Then
Selection.FillRight
End If
If Selection.Rows.Count 1 Then
Selection.FillDown
End If
End Sub


Also, can I assign a key in the code itself?

If you mean assigning a hot key: Tools/Macro (or Alt-F8) / Others
(bottom-right button

Finally, without going through conniptions with assigning security can I
set
it so that it runs the macro in any sheet I bring up?

This macro does run in any sheet!

Regards,
Stefi

I am not surprised, so place this line in the Workbook_Open() event procedure
to assign a hotkey:

Application.MacroOptions Macro:="UnMergeCopy", _
Description:="", HasShortcutKey:=True, ShortcutKey:="u"

Regards,
Stefi


€žAnil Gupte€� ezt Ã*rta:

That is working. Appreciate it.

What I meant about the Hot-Key selection is that it shoul dbe in th code
itself. I can just tell the users to load the sheet with the macro and
enter a key combination. You will be surprised how much work is invloved in
getting them to understand Tools-Macro-Choose the Macro-Options
blah-blah.

Anyway, thanx again.

--
Anil Gupte

www.keeninc.net
www.icinema.com
k.e.e.n., inc.
Milwaukee, WI, USA
+1-414/431-8775
Pune, MH, India
+91-20/30223100
"Stefi" wrote in message
...
This is a better tested version:
Sub UnMergeCopy()
Selection.MergeCells = False
On Error Resume Next
If Selection.Columns.Count 1 Then
Selection.FillRight
End If
If Selection.Rows.Count 1 Then
Selection.FillDown
End If
End Sub


Also, can I assign a key in the code itself?

If you mean assigning a hot key: Tools/Macro (or Alt-F8) / Others
(bottom-right button

Finally, without going through conniptions with assigning security can I
set
it so that it runs the macro in any sheet I bring up?

This macro does run in any sheet!

Regards,
Stefi