Hide and unhide rows based on cell value

[Simmo1313]

I have been trying to teach myself this through various forum threads but I have never used macros before so i'm not having any success.

I have a data range on sheet2 from A2:C16 and if any of the values in column C return a value of 0 I want to hide that row. If that value changes then I want to automatically unhide that row.

I want this macro to only have effect on sheet2 but I will more than likely want to use a similar hide macro on the other sheets within the same workbook but for different data ranges and other target columns. I'm hoping that I can use the same code but change the variables and insert it onto the other sheets?

If I could get a step by step process I would be extremely grateful as I am very much a novice.

 

[Smitty]

Welcome to the Board!

Have you tried AutoFilter? If you filter column C and uncheck 0's then you can hide those rows.

How is the value in column C changing? If it's via formula then you can record a macro applying the filter, then call it from the worksheet Calculate event:

Private Sub Worksheet_Calculate()
  Call Filtercode
End Sub

HTH,

 

[Simmo1313]

Thanks Smitty,

Sometimes the simplest solution is the best, I was way over thinking that.

The value in C is changing by formula. So I'm not sure how to insert that macro to make the filter auto refresh?

 

[Smitty]

The Calculate event fires each time the sheet calculates, so you can use it to call your filtering code. Note that the Calculate code needs to go in the worksheet specific module.

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Call</SPAN> FilterMe(3)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> FilterMe(col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>)<br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        ActiveSheet.UsedRange.AutoFilter Field:=col, Criteria1:="<>0", Operator:=xlFilterValues<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

This uses a variable to tell the filter which column to work on.

 

[Simmo1313]

I apologise for this but I have no idea how to input that macro or how to run it. This will be the first time I have used a macro for anything.

I have pressed alt+F11
right clicked the specific worksheet i want the macro to be active in and copy and pasted your code.
That's as far as I can get and it doesn't do anything so I'm sure i am missing steps.
The filter I want to update is in column C if that helps

Much appreciated

 

[Smitty]

The Calculate event code goes in the worksheet specific module. Then goto Insert-->Module and paste the FilterMe code there.

The code should fire when the worksheet calculates.

If you still have a problem, then PM me your e-mail address and I'll send you an example.