Unmerge+copy Macro

A

Anil Gupte

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,
 
S

Stefi

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:
 
A

Anil Gupte

Fantastic! Too Cool!

Thanx,
--
Anil Gupte
Stefi said:
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:
Click to expand...
 
A

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
[email protected]
www.keeninc.net
www.icinema.com
k.e.e.n., inc.
Milwaukee, WI, USA
+1-414/431-8775
Pune, MH, India
+91-20/30223100
 
A

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?
 
S

Stefi

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

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

This macro does run in any sheet!

Regards,
Stefi
 
S

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:
 
A

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
[email protected]
www.keeninc.net
www.icinema.com
k.e.e.n., inc.
Milwaukee, WI, USA
+1-414/431-8775
Pune, MH, India
+91-20/30223100
 
S

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:
 
Top