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

 

[Joe4]

Welcome to the Board!

A few things. In Cells notation, rows is the first argument, and columns is second, i.e.
Cells(row, column)

And if you start at the bottom (Rows.Count), you want to go up, not down.

So that highlighted line should be:
lc = Cells(Rows.Count,8).End(xlUp).Row
and your IF statement should be:
If Cells(c,8).Value = "complete" Then Rows(c).Hidden = True

 

[maureenboyd60]

If Cells(c,8).Value = "complete" Then Rows(c).Hidden = True

Thanks so much for your prompt response. I modified the code as you suggested and received a runtime error (429):
"ActiveX component cannot create object." Thanks!

Here is the code:

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(Rows.Count, 8).End(xlUp).Row
' Loop through column 8 and hide all columns with "complete"
Application.ScreenUpdating = False
For c = 1 To lc
If Cells(c, 8).Value = "complete" Then Rows(c).Hidden = True
Next c
Application.ScreenUpdating = True

End Sub

 

[Joe4]

There are no ActiveX components in that code.

Are you using any on your worksheet?
Are they located in column 8?

When you get that error message, do you have a "Debug" option?
If so, and you click it, which line of code does it highlight?

 

[maureenboyd60]

There are no ActiveX components in that code.

Are you using any on your worksheet?
Are they located in column 8?

When you get that error message, do you have a "Debug" option?
If so, and you click it, which line of code does it highlight?

Hi:
When I click Debug > Step into, I immediately get the ActiveX error. There is only one other macro that I can see and it is in the personal workbook.

I do not have access to the first AutoMacro. The highlighted Sheet1 is where the new Calculate macro is located. The next project has no macros and the Personal workbook has 1 macro which is sorting data by time (located in Module 1).

thanks!

 

[Joe4]

No, I meant is there a "Debug" option on the message that pops up when you get that error.

Let's take a step back. When do you get that error?
When you try to run the code?
Or when you try to Compile the code?

If you try to Compile the code (from the VB Editor, go to "Compile VBA Project" from the Debug menu, and see what line of code it jumps to.

 

[maureenboyd60]

No, I meant is there a "Debug" option on the message that pops up when you get that error.

Let's take a step back. When do you get that error?
When you try to run the code?
Or when you try to Compile the code?

If you try to Compile the code (from the VB Editor, go to "Compile VBA Project" from the Debug menu, and see what line of code it jumps to.

Hi:

When I click RUN, this is what I get:

Here is what I see when Sheet1 (for .xlsm macro) is highlighted and I click Debug:

 

[maureenboyd60]

Hi:

When I click RUN, this is what I get:

View attachment 38058

Here is what I see when Sheet1 (for .xlsm macro) is highlighted and I click Debug:
View attachment 38057

The compile option is only available when I click on the .XLSX project where there are no macros.

 

[Joe4]

There is nothing in that code that should cause that sort of error (it is not using any ActiveX controls), so I think your issue is actually somewhere else.
It could be with your Add-In, or in other code, or something that you have placed on one of the worksheets.

If you change your code to this temporarily, and then make a change/entry anywhere on Sheet1, what happens?

Private Sub Worksheet_Calculate()
    MsgBox "Change occurred"
End Sub

 

[Joe4]

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?