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
Bookmarks