Forum Discussion

gunner-b's avatar
gunner-b
Copper Contributor
Nov 20, 2021

Autofit Row Height when sorting

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 le...
Formulas and Functions
deniserTH's avatar
deniserTH
Copper Contributor
Feb 06, 2022

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.

 

 
  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Feb 06, 2022

    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)

     

    • PlotinusRedux's avatar
      PlotinusRedux
      Copper Contributor
      Jan 04, 2023

      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.

    • Kingram01's avatar
      Kingram01
      Copper Contributor
      Dec 21, 2022
      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?
      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Dec 21, 2022
        So it works for me (Excel 2016). Did you save the file as .Xlsm?

Resources