Using VBA to hide rows

[kleinmat]

Hi all,
I have a macro-enabled Excel Sheet that is basically a work planner and fundamentally looks like this (well, in reality with thousands of rows and over 100 columns):

What I now would like to achieve is to create a separate Tab that contains only those rows that say Team 1, Team 2 (the grey rows) and those that have a Task Description on them.
Like so:

Not sure if it has an impact, but:
1. The blue markings on the original tab are done through conditional formatting
2. The second Tab (the one with the schedule and the hidden rows) is fully protected (read only for the user)
3. That second Tab will also have rows and columns hidden by default (the usable space is over 10.000 rows long and over 100 columns wide, but anything below that last line ~10.000 and every column to the right of the ~100s column will be hidden anyways)

I tried several code fragments that I could find here in this forum, but none worked for me.
I should say that I am newish to Excel, so I apologize if this is trivial and I just don't know the solution.

Thank you so much!
Matt

 

[rpaulson]

try this.

Sub Hide()


Worksheets("Sheet2").Activate
ActiveSheet.Unprotect Password:="yourpassword"

lr = Cells(Rows.Count, "B").End(xlUp).Row

Rows("5:" & lr).Hidden = True 'first lets hide all the rows

For i = 4 To lr 'Cells(Rows.Count, "B").End(xlUp).Row
    If Cells(i, "A").Value <> "" Or Cells(i, "B").Value <> "" Then Rows(i).Hidden = False
    If Cells(i, "A").Value <> "" Then Rows(i - 1).Hidden = False
Next i

ActiveSheet.Protect Password:="yourpassword"

End Sub

hth,
Ross

 

[kleinmat]

Thank you! But how/when does that trigger?
I have only added that code (slightly modified to the actual sheet I use) into the VBA worksheet of that Schedule Tab (Sheet10), but it seems to never be executed.
Sorry, that is a newbie question - but that is what I am.
Thank you!
Matt

 

[rpaulson]

insert a shape, picture, icon, text box etc... anywhere on you worksheet.
Right click on the object you just made - selected assign macro. then click on the macro that you created.

now anytime you click on the object the macro will run.

 

[kleinmat]

Thank you. Does that even work within a fully protected sheet? I want Sheet10 to be fully read-only.

 

[kleinmat]

Regardless, I added a Shape and assigned this function. But when I press it, I get a "Subscript out of range" error message. Any idea how to debug that?
Thank you very much for your help!

 

[rpaulson]

you don't have a worksheet named "Sheet10". you renamed it to "Schedule"

change Worksheets("Sheet2").Activate to Worksheets("Schedule").Activate

 

[kleinmat]

PERFECT!

that works beautifully! Thank you so much!