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
alisonthomAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try

Sub ColAutofit()
Dim aRangeF() As Variant
Dim aRangeH() As Variant
For Each col In ActiveSheet.UsedRange.Columns
    aRangeF = Range(col.Address).Formula
    aRangeH = aRangeF
    For Idx = 1 To UBound(aRangeH, 1)
        aRangeH(Idx, 1) = IIf(Cells(col.Row + Idx - 1, col.Column).EntireRow.Hidden, "", aRangeF(Idx, 1))
    Next
    Range(col.Address) = aRangeH
    If col.Hidden = False Then
        col.EntireColumn.AutoFit
    End If
    colWidth = col.ColumnWidth
    Range(col.Address) = aRangeF
    col.ColumnWidth = colWidth
Next
End Sub

Sub RowAutofit()
Dim aRangeF() As Variant
Dim aRangeH() As Variant
For Each rw In ActiveSheet.UsedRange.Rows
    aRangeF = Range(rw.Address).Formula
    aRangeH = aRangeF
    For Idx = 1 To UBound(aRangeH, 2)
        aRangeH(1, Idx) = IIf(Cells(rw.Row, rw.Column + Idx - 1).EntireColumn.Hidden, "", aRangeF(1, Idx))
    Next
    Range(rw.Address) = aRangeH
    If rw.Hidden = False Then
        rw.EntireRow.AutoFit
    End If
    rwHeight = rw.Height
    Range(rw.Address) = aRangeF
    rw.RowHeight = rwHeight
Next
End Sub

Open in new window

Regards
0
 
Rgonzo1971Commented:
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
0
 
alisonthomAuthor Commented:
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
0
 
alisonthomAuthor Commented:
Hi

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

Thank you so much!!!
Alison
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.