9

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

2
  • I believe if you type <ALT+ENTER>, then Excel assumes you want to wrap text in the cell since you are specifying more than one line of text in it. You could always write a macro with a Worksheet Change for format the cell you just typed in as you want it to be. Nov 25, 2021 at 19:38
  • 1
    I reviewed a late answer that should be upvoted and accepted for future visitors.
    – Blindspots
    Feb 3, 2023 at 19:21

6 Answers 6

7

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


  1. Select the row(s) whose height you want to lock
  2. 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
  3. 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

1
  • 3
    Great example of when it is good to answer an old question! +10! No preexisting answers nailed it before you.
    – Blindspots
    Feb 3, 2023 at 19:20
0

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.

enter image description here

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

2
  • I still use 2007, and I don't see any Excel-related thing in Group Policy but it might be possible (I found some links about this) to add Group Policy for 2007, I'll try.
    – Basj
    Nov 26, 2021 at 8:29
  • Anyway, what does your technique do? What does it disable exactly? If it disables the ALT+ENTER shortcut, then it's not what I'm looking for. I still want to be able to do ALT+ENTER to have multiline text. But I just want to avoid the automatic enabling of Wrap text button, and automatic change of height of a row.
    – Basj
    Nov 26, 2021 at 8:30
0
  1. Ctrl + A to select all cells in the current worksheet
  2. Right click on any row number > Row Height > OK
  3. Right click on any row number > Format Cells > Vertical Alignment = Top > OK
2
  • 1
    This doesn't work. As soon as you edit one of the cells with line breaks and hit enter they revert to the text wrapping. Dec 13, 2022 at 15:45
  • @ToivoSäwén you are correct. The answer only works if all rows in the worksheet share identical heights. If even one row is different, the solution fails. This answer by user1769377 works properly and should be upvoted to help future visitors to this question.
    – Blindspots
    Feb 3, 2023 at 19:18
0

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".

"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

VBA code to disable text wrap everywhere on the sheet

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

VBA code to disable text wrap for a specific column

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

VBA code to disable text wrap for a specific row

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

VBA code to disable text wrap for a specific range


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.

0

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
-1

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.

1
  • Sadly no, it doesn't work. You can try and set all cells (CTRL A) to "Wrap text" unchecked. Then go in any cell. Write Hello <ALT+ENTER> World, and you'll see the "Wrap text" will come again, automatically, for this cell.
    – Basj
    Nov 25, 2021 at 17:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .