Autosize Row Height based on Visible cells only

[nimesh29]

I am trying to Autosize Row height based on Visible cells only and not the Hidden columns. I have columns that has very long text that I will be hiding and then I am left with extra white space in other cells and extra long worksheet.

I tried formatting the Autofit cells Row height but, it adjust it based on cells in hidden columns. I searched and found few codes but, nothing seems to do what I am trying to do.

Thanks for your help with this.

Nimesh

 

[shknbk2]

What if you format the cells of the hidden columns as NOT word wrapped? When hidden, the text that would extend beyond also gets hidden. I guess this won't work, however, if the cell value has newline characters (Alt+Enter).

 

[JoeMo]

Select the rows you want to resize, then run this. If the first row of the selection doesn't have a row height you like, pick another row (red font) or just use a numerical row height on the right side of the equal sign.

Sub test()
'change row number within selection to suit
Selection.RowHeight = Selection.Rows(1).RowHeight
End Sub

 

[nimesh29]

Thanks Joe this works for individual Rows. But, I am trying to change all affected rows in the sheet.
I found a code that will do that but, it doesn't play nice with Merged cells in one of the column when I hide the other column.

Code:

Sub AutoFitRow()

    Dim rngRow As Range
    Dim rngAutofit As Range
    Dim rngCopy As Range
    
    Set rngAutofit = Range("A501")
    For Each rngRow In Range("A2:A500").Rows
        rngRow.EntireRow.SpecialCells(xlCellTypeVisible).Copy rngAutofit
        rngAutofit.EntireRow.AutoFit
        rngRow.RowHeight = rngAutofit.RowHeight
    Next
    rngAutofit.EntireRow.Clear
    
End Sub

Link to File:
https://we.tl/t-Qrn77sNnJ9

 

[Majuba]

I am trying to Autosize Row height based on Visible cells only and not the Hidden columns. I have columns that has very long text that I will be hiding and then I am left with extra white space in other cells and extra long worksheet.

I tried formatting the Autofit cells Row height but, it adjust it based on cells in hidden columns. I searched and found few codes but, nothing seems to do what I am trying to do.

Thanks for your help with this.

Nimesh

Found a solution for this one.

1. Copy the sheet you want to adjust row height on.
2. On the copy, delete any columns that you don't want to affect height,
3. Autofit the row height on that sheet [make sure the minimum row height is acceptable as well, e.g. if there are empty rows.]
4. Create a macro - assign it a hot-key (e.g. Ctrl-Shift-H), save the macro
5. Edit the macro and use this, adjusting the sheet names to match yours:

Sub RowAdjust()
'
' RowAdjust Macro
' copy row height from one sheet to another
'
Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet1").Activate
ActiveCell.Select
Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet2").Activate
Selection.RowHeight = ActiveCell.Height
Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet1").Activate
ActiveCell.Offset(1).Select

End Sub

6. Start at the top row you want to adjust the height, on the first sheet, and hold down Ctrl-Shift-H (or whatever you set the hot key to).
7. Delete the Macro.