hide rows on different sheets

[Saintsman]

From my master sheet1 I want to select various rows to hide on a
corresponding sheet2 ie if sheet1!A3 = X then hide row 3 on sheet2; if
sheet1!A6=X then hide row 6 on sheet2 etc etc for any number of rows

thanks

 

[Nigel]

On sheet 1 try this. You might want a master reset to scan all column A on
sheet1 for X's then hide/unhide Sheet1. If you do repost.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = "X" Then
Sheet2.Rows(Target.Row).EntireRow.Hidden = True
Else
Sheet2.Rows(Target.Row).EntireRow.Hidden = False
End If
End If
End Sub

 

[Saintsman]

Nigel
Brilliant!!
Thanks for the very quick reply as well

 

[Patrick Molloy]

several ways to do this. You could use the sheet's change event or the
sheets calculate event.
Here's the code:

Option Explicit
Private Sub Worksheet_Calculate()
CheckForX
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then CheckForX
End Sub
Sub CheckForX()
Dim addr As String
Dim cell As Range
Set cell = Range("A:A").Find("X")
If Not cell Is Nothing Then
addr = cell.Address
Do
Worksheets("sheet2").Rows(cell.Row).Hidden = True
Set cell = Range("A:A").FindNext(cell)
Loop Until cell.Address = addr
End If
End Sub

to get to the sheet's code page, just right click the tab and select View
Code from the popup menu

whenever a cell's value changes - user entry - the change event fires -
triggering the change event ...

 

[Saintsman]

Nigel
The master reset to scan colA for X's and hide/unhide would be realy useful
as well!!
Thank you

 

[Nigel]

Try this, it is a variation of the solution posted by Patrick Molloy, with
the addition of all rows unhide code. Note this can be run from a standard
code module, the two sheets named Sheet1 and Sheet2, if different change
were referenced in the code.



Sub ResetHiddenRows()
Dim addr As String
Dim cell As Range

' clear hidden rows
With Sheets("Sheet2")
.Rows("1:" & .Rows.Count).EntireRow.Hidden = False
End With

' hide selected rows
With Sheets("Sheet1")
Set cell = .Range("A:A").Find("X")
If Not cell Is Nothing Then
addr = cell.Address
Do
Worksheets("Sheet2").Rows(cell.Row).Hidden = True
Set cell = .Range("A:A").FindNext(cell)
Loop Until cell.Address = addr
End If
End With
End Sub

--

Regards,
Nigel
(e-mail address removed)