If statement to hide/show rows in Excel VBA

问题:

I have a sequence of code where if C37 is blank, I want two series of rows to be hidden. The code I have works successfully for this.

However,

If D37 is not blank I would like the same series of rows to be unhidden.

'Show/Hide Filter Index Columns

If Worksheets("Req Sheet").Range("C37").Value = "" Then
   Worksheets("Formulation").Rows("54:57").EntireRow.Hidden = True
   Worksheets("Formulation").Rows("125:128").EntireRow.Hidden = True
Else
    Rows("54:57").EntireRow.Hidden = False
    Rows("125:128").EntireRow.Hidden = False

End If

If Worksheets("Req Sheet").Range("C38").Value = "" Then
   Worksheets("Formulation").Rows("54:57").EntireRow.Hidden = True
   Worksheets("Formulation").Rows("125:128").EntireRow.Hidden = True
Else
    Rows("54:57").EntireRow.Hidden = False
    Rows("125:128").EntireRow.Hidden = False

End If

I know I have the syntax of the code wrong, but the problem I am getting is that the second portion of code from C38 will supersede the code from the from C37.

I have tried using an and operator but I couldn't achieve success!

Thanks for your help!

回答1:

With Worksheets("Req Sheet")

    If .Range("C37").Value <> "" Or .Range("C38").Value <> "" Then
        Worksheets("Formulation").Rows("54:57").EntireRow.Hidden = False
        Worksheets("Formulation").Rows("125:128").EntireRow.Hidden = False
    Else
        Worksheets("Formulation").Rows("54:57").EntireRow.Hidden = True
        Worksheets("Formulation").Rows("125:128").EntireRow.Hidden = True

        Rows("54:57").EntireRow.Hidden = False
        Rows("125:128").EntireRow.Hidden = False
    End If

End With

回答2:

This code will hide the rows if C37 is empty and D37 has data.
It will unhide the rows if C37 has data and D37 is empty.
Any other condition will unhide the rows.

Sub Test()

    Dim rng As Range

    With ThisWorkbook
        Set rng = Union(.Worksheets("Formulation").Rows("54:57"), _
                        .Worksheets("Formulation").Rows("128:128"))

        With .Worksheets("Req Sheet")
            rng.EntireRow.Hidden = (.Range("C37") <> "" And .Range("D37") = "")
        End With

    End With

End Sub