Auto-hide columns / rows when Cell Values = 0

Hi Guys

I am trying to design a spreadsheet where columns on one sheet (rows on another sheet) are hidden/unhidden based on the value in the cell at the top (or far left) of the column (or row). I want this to run quickly and automatically when I change data in the spreadsheet. Each sheet is about 300 cells by 300 cells. I started using this simple formula:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
Sheets("Sheet1").Select
Range("A1:A300").Select
For Each cell In Selection
If cell = 1 Then
Range(cell.Address).EntireRow.Hidden = True
End If
Next
End Sub

It was working as a normal macro but when I changed it to Autorun (Application.ScreenUpdating = True) it works but does not let me update the spreadsheet as every time I move cell it re-runs!

The above was only a test and I actually want the rows/columns to hide when the corresponding value is 0 (it is never blank) and stay visible when the value is 1 or greater. Has anyone got any ideas?

I had seen this on the site but couldn’t get it to work as my VB skills aren’t sufficient

Sub watersev1()
Dim myrange, cl, c, i As Long, x
Application.ScreenUpdating = False
Set c = [aj1]: Set myrange = Range([aj1], Cells(Rows.Count, "AJ").End(xlUp))
For Each cl In myrange
If cl.Value = "" Then Set c = Union(c, cl)
Next: x = Split(Replace(Split(c.Address, "$AJ$1,")(1), "$AJ$", ""), ",")
For i = 1 To UBound(x)
If InStr(1, x(i), ":") = 0 Then x(i) = x(i) & ":" & x(i)
Next
Range(Join(x, ",")).EntireRow.Hidden = True: Rows(1).Hidden = IIf([aj1] <> "", False, True)
Application.ScreenUpdating = True: Set c = Nothing: Set myrange = Nothing: End Sub

Now I'm really confused. When back into a test spreadsheet and input the simpler version and it doesn't appear on the list

Macro.JPG

I am pretty new to all this so any help would be appreciated...

Right...got it. So I have input what was suggested but the removal of ByVal seemed to stop it working. As a result I used the below:

Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Sheets("Sheet1").Select
Range("A1:A300").Select
For Each cell In Selection
If cell = 1 Then
Range(cell.Address).EntireRow.Hidden = True
End If
Next
Application.EnableEvents = True
End Sub

This still gives me the problem that any time I click anywhere it runs the macro and leaves the range A1:A300 selected. Can I get the macro to run without it doing this? Basically I want the user to almost not realise that it is happening as they use the sheet.

Thanks for your help xsadept that is working. The problem is that maybe I didn't make it clear before but if a cell was a 1 and so was hidden but then changes to 0 due to other changes on the sheet then I would like the row/column to become unhidden. I changed to

Please Login or Register  to view this content.

The problem is that is buzzes around a lot and takes a while to update. Is there a better way that is more user friendly?