Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows.
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 On 11 Jan., 18:45, Raz wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows.
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 "Raz" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing rows not wanted... | Excel Discussion (Misc queries) | |||
Filtering and removing rows | Excel Programming | |||
Removing Empty Rows and selecting Specific Rows | Excel Programming | |||
Removing Blank Rows? | Excel Programming | |||
trouble removing rows | Excel Programming |