CODEWITHSUNDEEP
excelvba
zhuanzhou
zhuanzhou

Reputation: 2453

how to use the following excel vba code?

Sub MergeRanges()
    Dim rng As Range, txt As String
    For Each rng In Selection
        txt = txt & rng.Value2
    Next
    Application.DisplayAlerts = False
    Selection.Merge
    Selection = txt
    Application.DisplayAlerts = True
End Sub

when merge cells, i want to remain all the data in the cells. i googled some time,finding the above code. but i don't know how to use it? and what's the meaning of them. thank you.

Upvotes: 1

Views: 1200

Answers (3)

Gaijinhunter
Gaijinhunter

Reputation: 14685

Highlight some cells you want to merge (not dates or currency) and the press Alt+F8 an select this macro. It should (I didn't test) merge the cell contents into a cell without losing any of the contents that were in each cell.

Make sure to insert that code in the file first by pressing Alt+F11 to open VBE and right click the explorer and choose add module. Then paste this code in. Make sure your macro security is low enough to run it.

Upvotes: 2

Jean-François Corbett
Jean-François Corbett

Reputation: 38540

Setup:

  1. Open VBA editor (Tools > Macro > Visual Basic Editor) (shortcut Alt-F11)
  2. Insert > Module
  3. Paste the code into your new module.

Use:

  1. Select the cells you want to merge.
  2. Tools > Macro > Macros... (shortcut Alt-F8) > select MergeRanges > Run

If you want the macro to work with "special formats" such as dates, then you should change .Value2 to .Text.

Upvotes: 5

Doc Brown
Doc Brown

Reputation: 20054

  • in Excel, press Alt-F8

  • use the dialog to add an empty macro "MergeRanges"

  • add your code above.

  • To run the code, select some cells you want to merge, press Alt-F8 again and run that macro.

Upvotes: 2

Related Questions