Share via

Row Height changes after filtering

Jennifer Thomas 1 Reputation point
2022-01-28T17:01:13.007+00:00

Environment:
Excel Office 365 running in client on Windows 10
The worksheet has all columns set to Wrap Text so column width is retained.

Problem Description:

I show all and auto fit rows - everything looks fine.

Then I filter, and the rows no longer auto-fit; instead, the row height remains as it was when the list was unfiltered.

Question:
The rows are not the same height; how can I autofit again automatically after filtering?

Note: The filters are triggered by a macro, so potentially adding a line of code to that would be acceptable. Example:

Private Sub iManage_Click()
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Range("A1").AutoFilter Field:=6, Criteria1:="*Imanage*"

Range("A1:Z1000").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes

Range("A2").Select

End Sub

I can provide a sample but do not see how to do that at this point - if you need one, please say how to upload it.

Thanks!

Microsoft 365 and Office | Excel | For business | Windows
Developer technologies | Visual Basic for Applications

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Abdel Gonzalez 0 Reputation points
    2023-08-29T15:53:13.8433333+00:00

    This was driving me nuts but the clue was in the previous post: the macro. It just does something with the row height that I can't explain. The fix however was to embed code to resize the row height like I wanted.

    For example: 

        Rows("61:61").RowHeight = 54.6
    0 comments
  2. Michelle Trudeau 0 Reputation points
    2025-08-01T14:58:06.08+00:00

    I needed to restore the normal size of the first row after the end of a vba filtered list which is calculated in cell V3 with =SUMPRODUCT(MAX((C9:C1000<>"")*ROW(C9:C1000)). My filtering happens as an early part of the Print code but no effort to adjust row height within that code worked. Since I only need the row height to be correct when the workbook is reopened for editing, I made the simple module shown below and call it within Workbook_Open using Call Autofitrows.

    Sub Autofitrows()

    Sheet1.UnProtect Password:="Door8"

    Sheet1.Rows(Range("V3").Value).RowHeight = 18

    Sheet1.Protect Password:="Door8"

    End Sub

    Of course, you don't need the Unprotect/Protect if your Worksheet isn't protected. Also, my Workbook automatically saves and closes all open workbooks after running the Print process and other processes that Print triggers, so users never see and question the row that filtering squashes.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.