Autofit not autofitting...

[wpryan]

Hi All, I have the following code that is supposed to hide rows that contain certain criteria, and if they don't have the criteria, to autofit the content to the cell. The "cell" is a merged range. The "hidden" part of the code is working, but the autofit is not working however... Strange thing too, the autofit does not even work manually (double clicking the row)... Code is here:

 Set rng1 = Worksheets("Summary").Range("A19:A28").Cells
    Set rng2 = Worksheets("Summary").Range("A33:A42").Cells
    Set rngData1 = Worksheets("Data").Range("I2:I11").Cells
    Set rngData2 = Worksheets("Data").Range("H2:H11").Cells

    For Each rCell In rng1
        If (rCell.Value = "0" Or rCell.Value = "00.01.1900" Or rCell.Value = "" Or rCell.Value = "00:00:00") Then
            If rCell.EntireRow.Hidden = True Then
                GoTo 1
            Else:
                rCell.EntireRow.Hidden = True
            End If
        End If
1
    Next rCell
    For Each rCell In rng1
        If rCell.EntireRow.Hidden = False Then
            rCell.EntireRow.AutoFit
            
        End If
    Next rCell

    For Each rCell In rng2
        If (rCell.Value = "0" Or rCell.Value = "00.01.1900" Or rCell.Value = "" Or rCell.Value = "00:00:00") Then
            If rCell.EntireRow.Hidden = True Then
                GoTo 2
            Else:
                rCell.EntireRow.Hidden = True
            End If
        End If
2
    Next rCell
    For Each rCell In rng2
        If rCell.EntireRow.Hidden = False Then
            rCell.EntireRow.AutoFit
        End If
    Next rCell

 

[jmacleary]

Hi there. You don't say what version of office you are using, but I know that up to at least office 2010 autofittings of rows with merged cells didn't work. I had this a few years ago, and ended up writing a subroutine to work out how big to make the row height and then set it specifically in the macro. Unfortunately I no longer have access to that code.

 

[wpryan]

Hello, thanks for your response. I'm using Excel 2016...Maybe I have to do something like, if the length of the text is greater than X to set row height to Y...

 

[jmacleary]

Hi, yes, I think thats what I ended up having to do. You would have thought by 2016 they would have fixed it but it's microsoft so............. I will see if I can find the code I wrote (don't hold your breath).

 

[wpryan]

Hi, yes, I think thats what I ended up having to do. You would have thought by 2016 they would have fixed it but it's microsoft so............. I will see if I can find the code I wrote (don't hold your breath).

Don't worry, I can handle that bit. Thanks again for your reply!