asked on

Auto Hide using Excel Macro

Hi,

I'm trying to get excel to hide/unhide rows based on cell values

Sheet 1 Coloumn A has a list of values say 1 to 5
Sheet 2 references these values in Coloumn A: =Sheet1!A1

When a change is made to Sheet 1 it is reflected in sheet 2 and if the cell now has a value = 0 it hides the row. That bit works OK however if I revese the change to sheet 1 the row is not un-hidden on sheet2

Any help gratefuly recieved

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Last As Long
Dim Rng, c As Range
Last = Cells(Rows.Count, "C").End(xlUp).Row
Set Rng = Range("C1:C30" & Last)
Application.ScreenUpdating = False
For Each c In Rng
If c.Value = "0" Then
c.EntireRow.Hidden = True Or c.EntireRow.Hidden = False
End If
Next c
Application.ScreenUpdating = True
End Sub

Arno Koster

instead of hiding the row, you could also use the formula

=if(sheet1!A1 <> ""; Sheet1!A1; "")

this works but does not hide the complete row.

Rory Archibald

I suggest you use the Change event in Sheet1 to show/hide in sheet2 since formulas do not trigger the Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Last As Long
Dim Rng, c As Range
If Not Intersect(Target, range("C:C")) Is Nothing then
Application.ScreenUpdating = False
For Each c In Intersect(Target, range("C:C"))
   c.EntireRow.Hidden = (c.Value = 0)
   Sheets("Sheet2").Rows(c.Row).Hidden = (c.Value = 0)
Next c
Application.ScreenUpdating = True
End If
End Sub

Open in new window

in Sheet1 for example.

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial