Autofit Row Height when sorting

Copper Contributor

When a worksheet contains rows with different heights, I would expect those heights to be automatically adjusted during a sort of the rows in that sheet.  Alas, this appears to not be the case (At least I haven't been able to make that happen).  Can I make that happen?

19 Replies

@gunner-b 

You can manually adjust the column width or row height or automatically resize columns and rows to fit the data.

Change the column width or row height in Excel

 

Additional Information:

Change the column width and row height

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

 

 

Thanks for your help. I guess I'm just to lazy to want to do it manually.

@gunner-b 

Here automatic adjustment with VBA on all cells
See the attached file with examples where you can try it out yourself.

Enjoy excel

I experimented on an excel worksheet with rows of different heights and inserted a variety of objects (pictures, text boxes, etc,) and, sure enough, when I resorted the rows, autofit did not function. I also tried reformatting the objects . . . no luck! I'm satisfied. Thanks for looking at this with me.

@gunner-b 

I am pleased that you are satisfied, I hope to see you again in the future here in the forum.
Welcome to your Excel discussion space!

 

Also wish you have fun with Excel.

@NikolinoDE 

 

Hello Niko...

Your example file below is exactly what I'm trying to do.  But apparently I'm not be doing the VBA correctly, because it's not working for me either.  I even tried copying your code exactly, but it didn't do a thing.  (I've attached your example to clarify what I'm referring to.)   

Zeilen_Spalten_auto_VBA.xlsm
Preview file
20 KB
 
I resorted to creating a Macro/Button, but I'd rather it be done just as you have in your example, "Autofit Automatically".  I was even able to use your example and I added Data/Filter/Sort, and even after sorting, it still works!  
If there are some specific steps to this VBA Code, I'd be most interested.

 

 

@deniserTH 

VBA code "behind" a worksheet or a workbook may not work in Excel

o edit code "behind" a worksheet or a workbook:

  • Activate the Visual Basic Editor (press ALT+F11).

  • In the Project Explorer window, you will see entries similar to the following:

Copy this Code in the Worksheet you like

 

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireRow.AutoFit
Target.EntireColumn.AutoFit
   
End Sub

 

 With this you don't need a button or any other jump starter, the sheet should behave just like the sheet I sent you. If you cannot manage it on your PC, I can only help if you send me the file (without sensitive data).

 

*Knowing the Excel version, as well as the operating system and storage medium (OneDrive, hard drive, Sharepoint, etc.) would be an advantage to share more detailed information. 

 

Thank you for your understanding and patience

 

NikolinoDE

I know I don't know anything (Socrates)

 

NikolinoDE,
When looking at your formula above in black... I highlighted my entire sheet and typed just as yours above, which did not work. Should I replace some words with my excel worksheet/tab name?
I figured it out and it worked like a wonder! This is awesome!!!
So it works for me (Excel 2016). Did you save the file as .Xlsm?

@NikolinoDE I was able to get it to work on one of my workbooks and actually was able to remove column autofit and it still worked after sorting multiple different ways. Now my issue is.... I cant get it to do the same thing on another workbook by following the same exact example. It is odd. I have saved my add in and tried that as well with no luck. What am I doing wrong?

 

Right off the bat I can't suggest a solution to you, I could only give you guesses that might be very time-consuming. Please paste the file with the problem (and without sensitive data), that would help both of us to see where the error in this file could be.

@NikolinoDE  It will not let me attach the file. I have tried and it shows that it is not supported. 

thanks anyways :(
Which excel version is used?
If you are using excel for web you cannot edit VBA code only execute.
If you have no possibility add a file here.
You can use OneDrive, or any other internet option
.. try that too https://easyupload.io/

No luck

Dont think that is going to work either. we have the site you mentioned blocked and onedrive is only for internal docs only
As far as I can gather from your text, you are connected to a server. Files with VBA could be locked in the network. What and what options you have available, you should contact your administrator, the .xlsm files with VBA code could possibly be blocked for network security reasons.

It is always an advantage if users are informed about the Excel version, operating system and storage medium in advance. Beneficial for him as he could get a faster and more accurate solution suggestion and it is beneficial for the user who wants to suggest a solution to you.

Furthermore, I wish you much success with Excel.

@NikolinoDE 

 

Just for anyone else that runs into this, Worksheet_Change() did not work for me, but putting the code under Worksheet_SelectionChange() did even though I'm not actually changing the selection--it works even if I've got a random cell above the table selected.  <shrug>


Thanks for the tip, just needed to find the event that worked for me even though that event doesn't make any sense.  I just needed the row part, not the columns.