Excel Sort and Retain Row Height

  axelbrora 18:42 22 Apr 2008


I have a spreadsheet which is a list of varying heights of rows depending on the amount of text. There are about 6 columns and I am using Excel 2002 on Windows XP.

I have set autofit to all of the rows so that when I paste text into a row the row automatically adjusts in height to fit the text.

The problem is that when I sort the data by a different column the row height does not move with the data and the text does not fit. For example row 20 may be 25 points high to fit the data but when the data is re-sorted row 20 is still 25 points high and the data that ends up in this row may be a single line or numerous lines which are then partially hidden.

Is there way to tie the row height to the data so that when sorted they move together?


  VoG II 18:55 22 Apr 2008

You can run the following macro after the sort. Press ALT+F11 to open the Visual Basic Editor, Insert > Module. Copy and paste in

Sub RezizeRows()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Rows("1:" & LR).AutoFit
End Sub

Then close the VBE. With your sheet selected, Tools > Macro > Macros, highlight ResizeRows and click the Run button.

You can assign the code to a key combination or button on the worksheet if you wish.

  axelbrora 21:03 22 Apr 2008

Many thanks works a treat!


  VoG II 21:45 22 Apr 2008

Glad it works.

Now, if you wanted to be adventurous you could do the sort and row size adjustment all within one macro.

Tools > Macro > Record Macro

Then do your sort. Click the Stop recording button. ALT + F11 to open the VBE. The recorded macro will probably have been added to Module 2. Copy the code and paste it here.

Is it a static range that is sorted or do the rows/columns change?

  axelbrora 19:21 23 Apr 2008

Thanks for that.

The columns are static and set out to print on A4 landscape. The rows are constantly changing with more text being added the "comments" column. The sort column also varies between about six columns and sometimes the 1st, 2nd and 3rd sort options are all used, so yes it would need to be really adventurous to try and create macros.

Thanks anyway.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Best of the Grad Shows 2017: UAL Central Saint Martins

MacBook Pro 15-inch (2017) review

Comment connecter un MacBook à une TV ?