removing rows.

[Raz]

How to remove rows in one sheet according to values (text) entered in another
sheet.

If I do not enter text "PTA" (but anything else) in a sheet named "Input
Data", on another sheet named "2 Page" these rows (11,12,13,14,15,16,17,
20,21,22,23,24,25) should dissappear.

Also If I enter anything else, other than word "Vertical" in cell F3 of
"Input Data", rows 101,103,105,107,109,111,113 should dissappear in the sheet
named "2 Page"

when these conditions not true I want these rows to re-appear.

If I need to use macro or other codings please explain, As I am new to this.

Thanks in advance

 

[Per Jessen]

Hi

What should happen if the cell is blank, I assume rows are to be
hidden.

You do not say in which cell you want to check for PTA. In the code I
assume it is A1, but you can change it as required. The macro is not
case sensitive.

As this is a event macro it has to be inserted into the code sheet for
'Input Data' sheet. Right click on the sheet tab for 'Input Data',
click view code and insert the code below into the white code sheet.
Close the VBA editor and test it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value = "PTA" Then
Worksheets("2 Page").Rows("11:25").Hidden = False
Else
Worksheets("2 Page").Rows("11:25").Hidden = True
End If
End If
If Target.Address = "$F$3" Then
If Target.Value = "Vertical" Then
Worksheets("2 Page"). _
Rows("A101,A103,A105,A107,A109,A111,A113").Hidden = False
Else
Worksheets("2 Page"). _
Range("A101,A103,A105,A107,A109,A111,A113"). _
EntireRow.Hidden = True
End If
End If
End Sub

Hopes this helps.
....
Per

 

[JLGWhiz]

Copy this code and paste it into the Sheet named
"Input Data" code module. To access the code module,
right click the sheet tab and then click View Code
on the pop up menu.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("F3") And _
Target.Value <> "Vertical" Then
Sheets("Input Data") _
.Range("a101, a103, a105, a107, a109, a111, a113") _
.EntireRow.Hidden = True

ElseIf Target <> Range("F3") And Target.Value <> "PTA" Then
Sheets("2 Page").Rows("11:17").Hidden = True
Sheets("2 Page").Rows("20:25").Hidden = True
Else
Sheets("Input Data") _
.Range("a101,a103, a105, a107, a109, a111, a113") _
.EntireRow.Hidden = False
Sheets("2 Page").Rows("11:17").Hidden = False
Sheets("2 Page").Rows("20:25").Hidden = False
End If
End Sub