VBA How To Hide and Unhide rows

[kkoruni]

Hi all,
I am in need of your help with vba code below.
My problem is that I am running into (Run-Time error '91': Object Variable or With block variable not set). Currently, I have Cells E73, and E128 to control whether rows hide or not hide. The problem I discovered is from all other cells that contain the same drop-down selection words and the VBA thinks it is an action it needs to take.
I have a drop-down selection of "Included" and "Excluded" on Cells (E26, E40, E51, E62, E73, E84, E95, E106, E117, E128, E139, E150). However, I want the following Cells to trigger whether rows hide or not.
Can you please help me to mark only Cells E73 and E128 to drive if rows hide or not hide, while ignoring the other cell's selection even if it has same wording?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.CountLarge > 1 Then Exit Sub
Select Case Target.Address(0, 0)
Case "E73"
Set rng = Sheets("Proposal").Rows("349:403")
Case "E128"
Set rng = Sheets("Proposal").Rows("404:462")
End Select
Select Case Target.Value
Case "Included"
rng.Hidden = False
Case "Excluded"
rng.Hidden = True
End Select
End Sub

 

[Joe4]

The issue is it is hitting your second Case statement, regardless of what happens with the first.
So if it is not one of those two cells, then the rng variable is not set to anything.
But your second Case statement is trying to reference.

Just exit the sub after the first Case statement is the rng variable isn't set to anything, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    If Target.CountLarge > 1 Then Exit Sub

    Select Case Target.Address(0, 0)
        Case "E73"
            Set rng = Sheets("Proposal").Rows("349:403")
        Case "E128"
            Set rng = Sheets("Proposal").Rows("404:462")
    End Select

    If rng Is Nothing Then Exit Sub
    
    Select Case Target.Value
        Case "Included"
            rng.Hidden = False
        Case "Excluded"
            rng.Hidden = True
    End Select
    
End Sub

 

[kkoruni]

The issue is it is hitting your second Case statement, regardless of what happens with the first.
So if it is not one of those two cells, then the rng variable is not set to anything.
But your second Case statement is trying to reference.

Just exit the sub after the first Case statement is the rng variable isn't set to anything, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    If Target.CountLarge > 1 Then Exit Sub

    Select Case Target.Address(0, 0)
        Case "E73"
            Set rng = Sheets("Proposal").Rows("349:403")
        Case "E128"
            Set rng = Sheets("Proposal").Rows("404:462")
    End Select

    If rng Is Nothing Then Exit Sub
   
    Select Case Target.Value
        Case "Included"
            rng.Hidden = False
        Case "Excluded"
            rng.Hidden = True
    End Select
   
End Sub

Thank you for helping Joe4.
When I enter (If rng Is Nothing Then Exit Sub) to vba code, it only works for selection E73. However, for selection E128 it does nothing. Good thing is that I do not get any run-time errors. But, I am hoping for your help how to make E128 work.

 

[Joe4]

Do you know how to put break points in your code?
Put a break point on this line here:

    Select Case Target.Address(0, 0)

then make a change to cell E128.

That should cause the code to fire and then stop at that line.
Then use the F8 to proceed one line at a time.
Keeping pressing F8, watching the path it takes, and see if it goes like you expect.

 

[kkoruni]

Do you know how to put break points in your code?
Put a break point on this line here:

    Select Case Target.Address(0, 0)

then make a change to cell E128.

That should cause the code to fire and then stop at that line.
Then use the F8 to proceed one line at a time.
Keeping pressing F8, watching the path it takes, and see if it goes like you expect.

I figured out how to handle a breakpoint and view what i was missing.
I then, added another table "binder" on range for Case "E73". I followed the steps to make sure all codes match, but in one tab it works, on the other it does not.
So when i enter the ranges for my proposal and binder tabs while testing "excluded" for E73, the vba works for binder tab and does not work for proposal tab. Struggling with how one tab works and the other does not.
Do you know why?


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Exit if multiple cells updated at once
If Target.CountLarge > 1 Then Exit Sub
' See which cell was updated and set range
Select Case Target.Address(0, 0)
Case "E73"
Set rng = Sheets("Proposal").Rows("349:403")
Set rng = Sheets("Binder").Rows("350:404")
Case "E128"
Set rng = Sheets("Proposal").Rows("404:462")
Set rng = Sheets("Binder").Rows("405:463")
End Select
If rng Is Nothing Then Exit Sub

' Determine whether or not to hide/show rows
Select Case Target.Value
Case "Included"
rng.Hidden = False
Case "Excluded"
rng.Hidden = True
End Select

End Sub

 

[Joe4]

You will need two separate range variables!
Otherwise the second line just overwrites the first one, i.e. this:

Set rng = Sheets("Proposal").Rows("349:403")
Set rng = Sheets("Binder").Rows("350:404")

should be changed to something like this:

Set rng1 = Sheets("Proposal").Rows("349:403")
Set rng2 = Sheets("Binder").Rows("350:404")

And then you will need to hit both in your second block too, i.e.

Case "Included"
    rng1.Hidden = False
    rng2.Hidden = False

So you need to make those changes for the other one too.

 

[kkoruni]

You will need two separate range variables!
Otherwise the second line just overwrites the first one, i.e. this:

Set rng = Sheets("Proposal").Rows("349:403")
Set rng = Sheets("Binder").Rows("350:404")

should be changed to something like this:

Set rng1 = Sheets("Proposal").Rows("349:403")
Set rng2 = Sheets("Binder").Rows("350:404")

And then you will need to hit both in your second block too, i.e.

Case "Included"
    rng1.Hidden = False
    rng2.Hidden = False

So you need to make those changes for the other one too.

I completed the changes but does not work. Here is the whole vba code.
The way I see this is rng1 = proposal and rng2 = binder. But do we have to make any changes on "Dim rng As Range"?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.CountLarge > 1 Then Exit Sub
Select Case Target.Address(0, 0)
Case "E73"
Set rng1 = Sheets("Proposal").Rows("349:403")
Set rng2 = Sheets("Binder").Rows("350:404")
Case "E128"
Set rng1 = Sheets("Proposal").Rows("404:462")
Set rng2 = Sheets("Binder").Rows("405:463")
End Select
If rng Is Nothing Then Exit Sub

Select Case Target.Value
Case "Included"
rng1.Hidden = False
rng2.Hidden = False
Case "Excluded"
rng1.Hidden = True
rng2.Hidden = True
End Select

End Sub

 

[Joe4]

It is always best to declare all variables, so we should change that too (that is not what is causing the issue though).
But we also need to update our Exit line, as that is now hitting every time, as we are not setting "rng" to anything anymore.
(You would be able to see this is you try stepping through the code).

So here is what your complete code would look like.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng1 As Range
Dim rng2 As Range

If Target.CountLarge > 1 Then Exit Sub
    Select Case Target.Address(0, 0)
        Case "E73"
            Set rng1 = Sheets("Proposal").Rows("349:403")
            Set rng2 = Sheets("Binder").Rows("350:404")
        Case "E128"
            Set rng1 = Sheets("Proposal").Rows("404:462")
            Set rng2 = Sheets("Binder").Rows("405:463")
    End Select

If rng1 Is Nothing Then Exit Sub

Select Case Target.Value
    Case "Included"
        rng1.Hidden = False
        rng2.Hidden = False
    Case "Excluded"
        rng1.Hidden = True
        rng2.Hidden = True
End Select

End Sub

Also, in the future, please use Code tags when posting VBA code!
It maintains all formatting and makes the code much easier to read and follow.
See here: How to Post Your VBA Code

 

[kkoruni]

Private Sub Worksheet_Change(ByVal Target As Range) Dim rng1 As Range Dim rng2 As Range If Target.CountLarge > 1 Then Exit Sub Select Case Target.Address(0, 0) Case "E73" Set rng1 = Sheets("Proposal").Rows("349:403") Set rng2 = Sheets("Binder").Rows("350:404") Case "E128" Set rng1 = Sheets("Proposal").Rows("404:462") Set rng2 = Sheets("Binder").Rows("405:463") End Select If rng1 Is Nothing Then Exit Sub Select Case Target.Value Case "Included" rng1.Hidden = False rng2.Hidden = False Case "Excluded" rng1.Hidden = True rng2.Hidden = True End Select End Sub

Amazing. It worked.
So for every range we have to define unique Dim rng, and at exit sub i had to update to rng1.
Got it.

thank you so much Joe4. You are a rock star.

 

[Joe4]

So for every range we have to define unique Dim rng, and at exit sub i had to update to rng1.

Not quite.

It is not required that you declare all your variables with "Dim" statements before using them, but it not actually required you do it.
But by doing, it you ensure that only the "right" kind of data can be put in those variables (i.e. you cannot put strings in a range variable).
Also, if you turn on "Option Explicit", it will help catch any typos, and help in debugging.
I HIGHLY recommend doing this (most advanced programmers do). See: Option Explicit in Excel VBA

I hope it make sense why you had to change this line:

If rng1 Is Nothing Then Exit Sub

Originally, we were just setting one range (rng), so that was there to check if we were NOT updating E73 or E128, then rng was not being set to anything, and we wanted to bail out of the code at that point before the second CASE statement.

However, we replaced "rng" with "rng1" and "rng2". So "rng" is not being used anymore, so it will ALWAYS be empty, meaning you would NEVER get to the second CASE statement.
So we needed to change "rng" to "rng1" or "rng2". It does not matter which one we choose, as we always either set neither of them, or both of them.

Does that clarify things a bit?

thank you so much Joe4

You are welcome.