We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Auto Hide using Excel Macro

Medium Priority
1,026 Views
Last Modified: 2012-08-13
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


Comment
Watch Question

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.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.