Software

Ask something
Question Locked

Excel - How to make row height static.

0 helpful
11 replies

by bobbi.dodd

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have cells that contain more than 255 characters and have made that text show properly by entering hard returns at the ends of lines (as text wrap would not function properly-leaving lines of text hidden). This works fine and I can drag to adjust row height or type in a specific row height to show all the text (as, auto-fit doesn't work properly). The prolem is when the spreadsheet is sorted, the row heights won't stay-they auto-fit to a smaller height and cut off text from showing. Does anyone know a way around this?

11 replies

by boxfiddler Hot topic

by clicking in the top left corner of the spreadsheet. Once all cells are highlighted, on the menu bar click format - row - height and set the row height to whatever you want it to be. Save the changes and this should do the trick.

This does not apply to Office 07, the functions is still there somewhere, but I couldn't tell you where at the moment, I haven't played with it enough.

by boxfiddler Hot topic

by clicking in the top left corner of the spreadsheet. Once all cells are highlighted, on the menu bar click format - row - height and set the row height to whatever you want it to be. Save the changes and this should do the trick.

This does not apply to Office 07, the function is still there somewhere, but I couldn't tell you where at the moment, I haven't played with it enough.

oops... double post - sorry and edited this one for typo.

by bobbi.dodd

I had already tried your solution, but unfortunately not all rows are the same height. In one column, some cells contain a lot of info and others very little, so I want them to auto-adjust as much as possible. I've also tried making the rows with large text cells a static height, but that will change once a sort of any kind is done. The issue seems to be that the sort will undo the row height. Any more thoughts, ideas?

by boxfiddler Hot topic

all rows the same height and have them suto-adjust to content at the same time. The two options are mutually exclusive.

edit typo

by bobbi.dodd

I just want the height that has been set on certain rows (either by dragging the row bar or by manually setting a height #)to stay and NOT auto-adjust on a sort. We don't want to have to go back and manually readjust rows that have large amounts of text in a cell to be larger so that all the text will show. The large rows readjust down to some Excel pre-determined height every time a sort is run.

by boxfiddler Hot topic

I misunderstood. In that case, the only thing I can think of is that this is related to the hard line breaks you enforced.

Have you tried formatting the cells to auto-fit to content?

by bobbi.dodd

It seems the issue is that, on a sort, Excel wants to auto-adjust the row height to some pre-determined Excel default (based on number of characters it wants to display, which I am overriding by the hard line breaks, so that all the test will display)- and I can't get that auto-adjust on sort to NOT happen. Thanks for your help, though.

by boxfiddler Hot topic

more help. I've never run into that problem... If I come across any new info in my reading, I'll post it.

by boxfiddler Hot topic

using Word wrap along with adjusting the column width and getting rid of the hard line braks?

by wflather

I had the same problem: multiple rows with differing amounts of text. I created a sort button to sort the worksheet using a macro, but lost row height. Simple solution: Select the entire worksheet (click in the upper left), hover your mouse over a row until it changes from an arrow to a horizontal line and double click. All rows now re-auto fit to contents.
I made a new macro and recorded those actions, then copied that code and pasted it to the end of my Sort macro. Problem now solved.
Code in my particular macro was this:
Sub Sort()
'
' Sort Macro
'

'Start sort at row 3 to preserve my column headers
Rows("3:3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A3:A999") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A2:D999")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.LargeScroll Down:=1
'Now autofit all rows to content
Cells.Select
Cells.EntireRow.AutoFit
'Return focus to near top
Range("A3").Select
End Sub

This discussion is closed to new replies.