CODEWITHSUNDEEP
vbaexcel
Mike Barnes
Mike Barnes

Reputation: 4305

Hiding Rows based on named cell range value?

I would like to create a macro that looks at the value of a Named cell range and Hide/Unhide cells based on the value in that Named Cell. I have a data validated list that one can select (a) Yes and (b) No from, If the user Selects the Named Cell "Select"(Cell "A1") then (a) Yes then Rows 5-10 must be hidden, otherwise they must be unhidden. The macro Does not need to look and the whole cell Value, as in "(a) Yes", it must only look at "(a)"

This is my code so far but it produces an error that says

Object variable or With block variable not set

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range

Cell = Range("Select")

If Target.Address = Cell Then
    Select Case Left$(Cell.Value, 3)
        Case "(a)"
            Rows("5:10").Hidden = True
    End Select
Else 
    Rows ("5:10").Hidden = False
End If
End Sub

How would one do something like this?

Upvotes: 0

Views: 2213

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Change the following:

Cell = Range("Select")

into:

Set Cell = Range("Select")

Moreover, you don't get expected result if you compare:

Target.Address = Cell

which never would be met. Try the following:

Target.Address = Cell.Address

And one more suggestion. You could combine both if and select conditions into one:

If Target.Address = Cell.Address And Left$(Cell.Value, 3) = "(a)" Then

Finally, remember that "(a)" <> "(A)" due to case sensitivity in VBA.

Upvotes: 1

Related Questions