Prevent Excel to automatically "Wrap text" in a cell

Question

Let's say we have a spreadsheet with all rows having the same default height.

In a cell, let's write Hello ALT+ENTER World. I indeed sometimes need to enter multi-line text in a cell, to write some long notes, etc.

Then this cell will automatically have the Wrap text button enabled (in the Home Ribbon menu), and this specific row will automatically have its height increased.

How to disable this automatic Wrap text? without having to manually click on Wrap text again to disable it?

See also (linked questions without a perfect solution for this problem):

score 12 · Answer 1 · edited Feb 03 '23 at 10:09

Set Fixed Row Height(s)

In order to stop row heights from increasing to accommodate content containing ALTENTER line breaks you can set the row height(s) to a fixed value.

_{Row Height: Fixed Value vs. Default}

Instructions

Select the row(s) whose height you want to lock
Open the Row Height dialog using one of the following approaches:  _{^{See images that follow}}
A. Ribbon Bar: Select Home > Format > Row Height
B. Mouse: Right-click row(s) > left-click Row Height
C. Keyboard: ALT+H then OH
Choose OK to accept the prepopulated row height or set a numeric value of your choice then click "OK" _{^{* See NOTES Below}}

Notes

ROW HEIGHT Dialog Box ( Instructions: Step 3 )

With a single row selected, the Row Height dialog box will always have the Row height field prepopulated with the row's current height.
With multiple rows selected, the field will be prepopulated only if all have identical heights.
Any time the field is prepopulated, one can immediately select OK (editing the field value becomes [optional]).
In the case where the heights of multiple rows do not match, the Row height field will be empty. In that case a value must be entered before choosing OK. Otherwise, it will be equivalent to choosing CANCEL.

Ribbon Bar Approach

_{Home > Format > Row Height}

Right-Click Approach

_{Right-Click row(s)}

Keyboard Approach

_ALT+H

_{then O}

_{then H}

score 0 · Answer 2 · answered Nov 26 '21 at 05:52

Could you please tell us which verion of Office are you using?

If you have one of Professioanl Plus versions, you could try the Group Policy to disable this shortcut.

User Configuration > Administrative Templates > Microsoft Excel 2016 > Disable Items in User Interface > Custom, enable "Disable shotcut keys". Then enter following "[key],[modifier]" values:13,16.

For more information, please refer to "Use Group Policy to disable user interface items and shortcut keys in Office 2013".

score 0 · Answer 3 · answered Dec 09 '22 at 22:05

0

Ctrl + A to select all cells in the current worksheet
Right click on any row number > Row Height > OK
Right click on any row number > Format Cells > Vertical Alignment = Top > OK

answered Dec 09 '22 at 22:05

Argo Saputro

9

score 0 · Answer 4 · answered Oct 17 '23 at 20:13

If you want to prevent "Wrap Text" and changing row height is not your option, then VBA is quite a flexible solution.

Go to the sheet module first. Click on a sheet where you need to prevent words wrap, right click on it, select "View code".

Depending on your needs, you may disable Text Wrap:

a) entirely on the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.WrapText = False
Application.ScreenUpdating = True
Target.Activate


End Sub

b) for specific column(s).
Here is a code that disable Text wrap in column "K":

If Target.Column = Range("K1").Column Then
    Application.ScreenUpdating = False
    Target.WrapText = False
    Application.ScreenUpdating = True
    Target.Activate
End If

c) for specific row(s).
Below, Text wrap is disabled for row 10:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.row = 10 Then
    Application.ScreenUpdating = False
    Target.WrapText = False
    Application.ScreenUpdating = True
    Target.Activate
End If
End Sub

d) for a range.
In this example, that range is "B30:D100":

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B30:D100")) Is Nothing Then
    Application.ScreenUpdating = False
    Target.WrapText = False
    Application.ScreenUpdating = True
    Target.Activate
End If
End Sub

Changing Application.ScreenUpdating from True to False in alignment with Target.Activate make you stick to the cell you work with. Otherwise, the window will flicker because Excel enables wrapping by default, and then disables it after executing the code from the sheet module. This little trick helps stay focused.

score 0 · Answer 5 · answered Nov 20 '23 at 23:42

Here is another method created when looking for a solution. I'm not sure anyone would go to this effort but, it may give some an idea. The workbook must be macro enabled (XLSM) and the code needs to be put into the worksheet. Then, the cell(s) must be selected. No disabling of actions such as screen updating were included. Enjoy.

Private Sub Worksheet_Change(ByVal Target As Range)
'If even one cell in the selection has text wrapped, and
'if even one cell in the selection is changed
'then all cells in the selection have WrapText turned off
'Intersect Logic Source
'https://stackoverflow.com/questions/15337008/excel-vba-run-macro-automatically-whenever-a-cell-is-changed
'Modified to prevent Wrap Text from being enabled when cell becomes dirty
'
'Range.WrapText property short description
'  It matters not how many cells in the range contain data, if any
'  if Me.Range("A1:B3").WrapText =
'     True:  All cells are wrapped
'     False: No cell is wrapped
'     Null:  at least one, but not all, cells are wrapped
On Error GoTo ErrorRoutine
   Dim lngRow1 As Long
   Dim lngRow2 As Long
   Dim lngCol1 As Long
   Dim lngCol2 As Long
   Dim rngSelected As Range
'of course, these values would most likely be determined
   lngRow1 = 1
   lngRow2 = 3
   lngCol1 = 1
   lngCol2 = 2
'  The example demonstrates two formats, for "A1:B3", the first fixed and the second calculated
'  The intersect could, of course, consist of a single cell ("A2")
   If Intersect(Target, Me.Range("A1:B3")) Is Nothing Then Exit Sub   'I'm unsure how this line helps
   Application.EnableEvents = False 'to prevent endless loop
Set rngSelected = Selection         'Reselect their selection
   Me.Range(Cells(lngRow1, lngCol1), Cells(lngRow2, lngCol2)).Select
   With Selection
      If .WrapText = False Then
         GoTo ExitRoutine
      Else
         .WrapText = False
      End If
   End With
ExitRoutine:
   Application.EnableEvents = True    're-enable the events
   rngSelected.Select
   Set rngSelected = Nothing
   Exit Sub
ErrorRoutine:
   MsgBox Err.Number & ": " & Err.Description
   Resume ExitRoutine
End Sub

score -1 · Answer 6 · answered Nov 25 '21 at 16:57

Maybe an answer:

The "Format Cells" dialog, i.e. CTRL+1 has an Alignment tab, where you find [x] Wrap Text...

To set or Reset that option for the entire sheet; CTRL+A, CTRL+1 and remove/set [x] Wrap Text

This might help...

I can imagine that you have Wrap Text enabled for any adjacent cell when the "problem" occurs.