How to stop excel revealing the hidden rows when double clicking on a range of row headers?

[arxoon]

I have some rows that I have hidden. I need to adjust the row height of some rows. When I select the row headers including the hidden rows and double click to automatically adjust the row height, the hidden rows get unhid. How to stop this beheaviour of excel?

 

[footoo]

Select the rows then go to Home>Editing>Find&Select>GoTo...>Special>Visible Cells Only>OK. Then double click to resize the rows.
If you need to do regularly, you can add a Select Visible Cells button to the QAT.

 

[arxoon]

Is it a normal behaviour? I don't remember hidden rows getting unhid when double clicking to adjust row heights.

 

[footoo]

Is it a normal behaviour? I don't remember hidden rows getting unhid when double clicking to adjust row heights.

Yes, I think it's normal - if you select the hidden rows.

 

[arxoon]

Is there any way to stop excel from unhiding such hidden rows on double clicking?

 

[footoo]

Here's a macro :

Sub V()
Selection.SpecialCells(xlCellTypeVisible).Rows.AutoFit
End Sub

I suggest that you put a button on the QAT.
Then select the rows (including the hidden rows) and click the button.

 

[arxoon]

Could you please tell me how to put the button in quick access toolbar?

 

[Joe4]

Could you please tell me how to put the button in quick access toolbar?

See here: Assign a macro to a button - Microsoft Support

 

[Jaafar Tribak]

To make sure the hidden rows remain hidden automatically without the need for buttons etc, you can use the following trick :

1- Select the rows that you want to be hidden and give them a Range Name (such as HiddenRows). You can do this via the Names dropdown located to the left of the formula bar. (circled in red in the image below) and then press the Enter key.

In this example, we will assume the hidden rows are 11:13

2- Once the named range is defined for the hidden rows as described above, add a formula with a volatile function to a cell in the worksheet. Easiest one is probably the formula =Now() ... I would place this formula in some cell at the far bottom of the worksheet so it can't be seen.

3- Hide the Rows.

4- Place this code in the Worksheet Module:

Option Explicit

Private Sub Worksheet_Calculate()
    If IsRangeVisible(Range("HiddenRows")) Then
        Range("HiddenRows").EntireRow.Hidden = True
    End If
End Sub

Private Function IsRangeVisible(ByVal oRange As Range) As Boolean
    Dim lRowsCount As Long
    On Error Resume Next
    lRowsCount = oRange.EntireRow.SpecialCells(xlCellTypeVisible).Count
    IsRangeVisible = CBool(lRowsCount)
End Function

From now on, the rows should remain hidden no matter what.

To unhide the rows, you could temporarly comment out the code inside the Worksheet_Calculate event.