asked on

Excel VBA - autofit row height and column width, where there are hidden rows and hidden columns

Hi

I have worksheets that have both hidden rows and hidden columns. When I use autofit row height and autofit column width the hidden rows and hidden columns are not ignored. For example, if cell G5 is non-blank and column G is hidden, the autofit row height will return a row height that does not ignore the hidden status of column G.

It would be really useful, and much appreciated, if there was an approach I could use in VBA that sets the autofit row height and autofit column width, ignoring hidden columns and hidden rows.

Thanks in advance
Alison

Rgonzo1971

HI,

pls try EDIT corrected code

Sub macro1()
For Each col In ActiveSheet.UsedRange.Columns
    If col.Hidden = False Then
        col.EntireColumn.AutoFit
    End If
Next
For Each rw In ActiveSheet.UsedRange.Rows
    If rw.Hidden = False Then
        rw.EntireRow.AutoFit
    End If
Next
End Sub

Open in new window

Regards

alisonthom

ASKER

Hi

Thanks very much for the response and code. I can see what your code is doing however I still have a problem. I should have provided an example file - apologies.

I have attached an example file in which cell E2 has text, but column E is hidden.
Also in the example file. cell B8 has text, but row 8 is hidden.

When I run the macro the row height of row 2 is 75, but I would like it to be 30 (i.e. as if cell E2 was blank)

Similarly, after running the macro the column width of column B is 35.86, but I would like it to be 14.29 (i.e. as if cell B8 was blank)
Autofit-example.xls

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alisonthom

ASKER

Hi

This is super!! It works perfectly and does exactly what I was trying to achieve.

Thank you so much!!!
Alison