CODEWITHSUNDEEP
vbaexcelif-statementexcel-2010
Mark
Mark

Reputation: 47

If-else VBA statement for auto-hiding rows in Excel2010

I am trying to hide-auto hide some rows in my Excel 2010 spreadsheet, based on a value that is entered in a specific field. I am using data validation to set the minimum and maximum number between 0 and 50. If it is 0, then hide the next 3 rows, but if it is any other number, show the rows.

Below is the VBA code that works, except that I have to put it each possible number. Is there an IF Else statement I can use instead? If 0 then Hide, Else show the rows

If Target.Address(False, False) = "B45" Then

    Select Case Target.Value

        Case "0": Rows("46:48").Hidden = True

        Case "1": Rows("46:48").Hidden = False

        Case "2": Rows("460:48").Hidden = False

        Case "3": Rows("46:48").Hidden = False

        Case "4": Rows("46:48").Hidden = False

        Case "5": Rows("46:48").Hidden = False

        Case "6": Rows("46:48").Hidden = False

        Case "7": Rows("46:48").Hidden = False

        Case "8": Rows("46:48").Hidden = False

        Case "9": Rows("46:48").Hidden = False

        Case "10": Rows("46:48").Hidden = False

    End Select

    Else: Rows("46:48").Hidden = False

End If

Thank you!

Upvotes: 1

Views: 2549

Answers (2)

Ripster
Ripster

Reputation: 3585

Instead of using Select Case You can try:

If Target.Address(False, False) = "B45" Then
    If Target.Value > 0 And Target.Value <= 10 Then
        Rows("46:48").Hidden = False
    Else
        Rows("46:48").Hidden = True
    End If
Else
    Rows("46:48").Hidden = False
End If

Upvotes: 0

user1064180
user1064180

Reputation:

Would you not just use:

Case Else
Rows("46:48").Hidden = False

?

This then says that anything that doesn't fit the first case (0) just defaults to this.

Sorry if I've misunderstood.

Upvotes: 1

Related Questions