How to wrap Excel text using a macro

Formats determine how Excel displays data and they're easy enough to apply. Simply select the appropriate cells, open the Format menu, and proceed accordingly.

Word has a neat feature that puts format choices on screen--Reveal Formats. Unfortunately, there's no such feature in Excel. If you apply (or remove) formats frequently, you might want to use a macro to toggle your more frequent formatting needs. For instance, the following macro toggles the Text Wrap format for a single cell, a range of cells, or even a non-contiguous range:

Sub WrapText()
  Dim rng As Range
  For Each rng In Selection
    rng.WrapText = Not rng.WrapText
End Sub

Apply a keyboard shortcut, such as Ctrl+w and you're ready to go. To create a keyboard shortcut for a macro, do the following:

  • Choose Macro from the Tools menu, and then select Macros.
  • In the Macro dialog box, click Options.
  • In the Macro options dialog box, enter a letter to combine with Ctrl.
  • Click OK.

Run the macro to apply or remove the Text Wrap format. Because it toggles the format property, you don't need two macros--one to apply and another to remove the format--this one does both.

You can use this macro to toggle most format properties. When entering the code, use Intellisense to view all the possible format properties for the defined variable.