how to hide rows based on a value in a column

[maureenboyd60]

Hello:
Thanks in advance for your help! I am new to VBA so my question may be quite simple! I have a range of data (Columns A:I) and an indeterminate number of rows. If the value in Column H = "complete' or "closed" I want to hide that row. Once the row is hidden, would it still show up in a search? I tried copying a macro I found on your site but it is not working. I assume I did not make the proper adjustments.
this is the macro I copied and tired to change. (I only tried it where cell in Column H = "complete"). The bolded row is where I believe the error is.

thanks!

Private Sub Worksheet_Calculate()

Dim lc As Long
Dim c As Long

' First unhide all rows
Cells.EntireRow.Hidden = False
' Find last row in column 8 with data
lc = Cells(8, Rows.Count).End(xldown).Row
' Loop through column 8 and hide all columns with "complete"
Application.ScreenUpdating = False
For c = 1 To lc
If Cells(8, c).Value = "complete" Then Cells(8, c).EntireRow.Hidden = True
Next c
Application.ScreenUpdating = True

End Sub

 

[maureenboyd60]

Something else just occurred to me.
"Worksheet_Calculate" code is event procedure code that runs automatically.
So you actually should not be able to "step into it", or run it manually.
You said the code is in the "Sheet1" module, right?

HI:
The test macro ran correctly. Yes the macro is in Sheet1. I had the filter tool turned on in the spreadsheet so I turned that off and I was able to see the Compile VBA project option. When I click it for either macro, I do not get any messages so I assume it compiled correctly?

 

[maureenboyd60]

Something else just occurred to me.
"Worksheet_Calculate" code is event procedure code that runs automatically.
So you actually should not be able to "step into it", or run it manually.
You said the code is in the "Sheet1" module, right?

Hi:
I was able to step into the macro and the error displayed right after I pressed F8 to continue:

 

[Joe4]

It looks like you are still using your original version of the code instead of using the updated code I gave you!
Try using the code that I gave you.
Copy and paste my version of the code, so as to not make any typos.

 

[maureenboyd60]

Hi:
I was able to step into the macro and the error displayed right after I pressed F8 to continue:
View attachment 38060

I put a watch on that line and noticed that the 'c' variable was 'out of context (in watch window at bottom)

 

[Joe4]

I put a watch on that line and noticed that the 'c' variable was 'out of context (in watch window at bottom)
View attachment 38061

That is because you are using your original bad code and not my updated code (see my previous post)!

"Rows.Count" takes you down to the last possible row on the sheet. You cannot do down from there!

 

[maureenboyd60]

It looks like you are still using your original version of the code instead of using the updated code I gave you!
Try using the code that I gave you.
Copy and paste my version of the code, so as to not make any typos.

OMG! sorry about that! With all the cutting and pasting, I somehow pasted back in the old version! It is now working! Thanks so much for your patience. I learned a lot! Take care.

 

[Joe4]

You are welcome.