Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple code hide/show rows with cell = empty, set value or any val
I currently have the code below in my worksheet, surely I can use consistent
coding to get what I need. I need to be able to show/hide various rows (sometimes single rows, sometimes blocks of rows and sometimes multiple rows not in order) which sometimes is based on a cell being empty, a cell being a set value/not a set value or being any value (not blank) Any suggestions for improvements? Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("E27") If Not Intersect(rng, Target) Is Nothing Then Rows(31).EntireRow.Hidden = IsEmpty(rng.Value) Rows(39).EntireRow.Hidden = IsEmpty(rng.Value) End If Select Case UCase(Range("B6").Value) Case "PBO" [35:36].EntireRow.Hidden = True [47:48].EntireRow.Hidden = True Case Else [35:36].EntireRow.Hidden = False [47:48].EntireRow.Hidden = False End Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple code hide/show rows with cell = empty, set value or any val
Minimal testing:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, rng2 As Range Set rng = Me.Range("E27") Set rng2 = Me.Range("B6") If Not Intersect(rng, Target) Is Nothing Then [31:31, 39:39].EntireRow.Hidden = IsEmpty(rng.Value) End If [35:36, 47:48].EntireRow.Hidden = (rng2.Value = "PBO") End Sub Regards, Greg "ivory_kitten" wrote: I currently have the code below in my worksheet, surely I can use consistent coding to get what I need. I need to be able to show/hide various rows (sometimes single rows, sometimes blocks of rows and sometimes multiple rows not in order) which sometimes is based on a cell being empty, a cell being a set value/not a set value or being any value (not blank) Any suggestions for improvements? Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("E27") If Not Intersect(rng, Target) Is Nothing Then Rows(31).EntireRow.Hidden = IsEmpty(rng.Value) Rows(39).EntireRow.Hidden = IsEmpty(rng.Value) End If Select Case UCase(Range("B6").Value) Case "PBO" [35:36].EntireRow.Hidden = True [47:48].EntireRow.Hidden = True Case Else [35:36].EntireRow.Hidden = False [47:48].EntireRow.Hidden = False End Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple code hide/show rows with cell = empty, set value or any
what if I wanted to match two cells in one condition with different criteria?
I want the second part to only initialise if rng3 does not = Shane and rng4 = blank or zero? I have this code so far: 'Hides depending on User' [7:7, 40:42].EntireRow.Hidden = (rng3.Value = "Shane") [8:19, 37:39].EntireRow.Hidden = (rng3.Value = "Dane") [8:19, 37:39].EntireRow.Hidden = (rng3.Value = "Elizabeth") 'Hides depending on Pack Level' If Not Intersect(rng4, Target) Is Nothing Then [8:19].EntireRow.Hidden = Not IsEmpty(rng4.Value) End If "Greg Wilson" wrote: Minimal testing: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, rng2 As Range Set rng = Me.Range("E27") Set rng2 = Me.Range("B6") If Not Intersect(rng, Target) Is Nothing Then [31:31, 39:39].EntireRow.Hidden = IsEmpty(rng.Value) End If [35:36, 47:48].EntireRow.Hidden = (rng2.Value = "PBO") End Sub Regards, Greg "ivory_kitten" wrote: I currently have the code below in my worksheet, surely I can use consistent coding to get what I need. I need to be able to show/hide various rows (sometimes single rows, sometimes blocks of rows and sometimes multiple rows not in order) which sometimes is based on a cell being empty, a cell being a set value/not a set value or being any value (not blank) Any suggestions for improvements? Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("E27") If Not Intersect(rng, Target) Is Nothing Then Rows(31).EntireRow.Hidden = IsEmpty(rng.Value) Rows(39).EntireRow.Hidden = IsEmpty(rng.Value) End If Select Case UCase(Range("B6").Value) Case "PBO" [35:36].EntireRow.Hidden = True [47:48].EntireRow.Hidden = True Case Else [35:36].EntireRow.Hidden = False [47:48].EntireRow.Hidden = False End Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple code hide/show rows with cell = empty, set value or any
This was very quick and dirty because I have to pack it in. I likely have the
logic wrong but I think the approach is correct. I'll leave it to you to correct the condition statements assuming I have them wrong: Dim rng3 As Range, rng4 As Range Dim wf As WorksheetFunction Dim cond1 As Boolean, cond2 As Boolean Set wf = Application.WorksheetFunction Set rng3 = Me.Range("E27") Set rng4 = Me.Range("B6") cond1 = (UCase(rng3.Value) < "SHANE") cond2 = Not IsEmpty(rng4) If Not Intersect(rng4, Target) Is Nothing Then [8:19].EntireRow.Hidden = wf.And(cond1, cond2) End If Greg "ivory_kitten" wrote: what if I wanted to match two cells in one condition with different criteria? I want the second part to only initialise if rng3 does not = Shane and rng4 = blank or zero? I have this code so far: 'Hides depending on User' [7:7, 40:42].EntireRow.Hidden = (rng3.Value = "Shane") [8:19, 37:39].EntireRow.Hidden = (rng3.Value = "Dane") [8:19, 37:39].EntireRow.Hidden = (rng3.Value = "Elizabeth") 'Hides depending on Pack Level' If Not Intersect(rng4, Target) Is Nothing Then [8:19].EntireRow.Hidden = Not IsEmpty(rng4.Value) End If "Greg Wilson" wrote: Minimal testing: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, rng2 As Range Set rng = Me.Range("E27") Set rng2 = Me.Range("B6") If Not Intersect(rng, Target) Is Nothing Then [31:31, 39:39].EntireRow.Hidden = IsEmpty(rng.Value) End If [35:36, 47:48].EntireRow.Hidden = (rng2.Value = "PBO") End Sub Regards, Greg "ivory_kitten" wrote: I currently have the code below in my worksheet, surely I can use consistent coding to get what I need. I need to be able to show/hide various rows (sometimes single rows, sometimes blocks of rows and sometimes multiple rows not in order) which sometimes is based on a cell being empty, a cell being a set value/not a set value or being any value (not blank) Any suggestions for improvements? Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("E27") If Not Intersect(rng, Target) Is Nothing Then Rows(31).EntireRow.Hidden = IsEmpty(rng.Value) Rows(39).EntireRow.Hidden = IsEmpty(rng.Value) End If Select Case UCase(Range("B6").Value) Case "PBO" [35:36].EntireRow.Hidden = True [47:48].EntireRow.Hidden = True Case Else [35:36].EntireRow.Hidden = False [47:48].EntireRow.Hidden = False End Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple code hide/show rows with cell = empty, set value or any
why doesn't my hide/unhiding work when i protect the sheet?
"Greg Wilson" wrote: This was very quick and dirty because I have to pack it in. I likely have the logic wrong but I think the approach is correct. I'll leave it to you to correct the condition statements assuming I have them wrong: Dim rng3 As Range, rng4 As Range Dim wf As WorksheetFunction Dim cond1 As Boolean, cond2 As Boolean Set wf = Application.WorksheetFunction Set rng3 = Me.Range("E27") Set rng4 = Me.Range("B6") cond1 = (UCase(rng3.Value) < "SHANE") cond2 = Not IsEmpty(rng4) If Not Intersect(rng4, Target) Is Nothing Then [8:19].EntireRow.Hidden = wf.And(cond1, cond2) End If Greg "ivory_kitten" wrote: what if I wanted to match two cells in one condition with different criteria? I want the second part to only initialise if rng3 does not = Shane and rng4 = blank or zero? I have this code so far: 'Hides depending on User' [7:7, 40:42].EntireRow.Hidden = (rng3.Value = "Shane") [8:19, 37:39].EntireRow.Hidden = (rng3.Value = "Dane") [8:19, 37:39].EntireRow.Hidden = (rng3.Value = "Elizabeth") 'Hides depending on Pack Level' If Not Intersect(rng4, Target) Is Nothing Then [8:19].EntireRow.Hidden = Not IsEmpty(rng4.Value) End If "Greg Wilson" wrote: Minimal testing: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, rng2 As Range Set rng = Me.Range("E27") Set rng2 = Me.Range("B6") If Not Intersect(rng, Target) Is Nothing Then [31:31, 39:39].EntireRow.Hidden = IsEmpty(rng.Value) End If [35:36, 47:48].EntireRow.Hidden = (rng2.Value = "PBO") End Sub Regards, Greg "ivory_kitten" wrote: I currently have the code below in my worksheet, surely I can use consistent coding to get what I need. I need to be able to show/hide various rows (sometimes single rows, sometimes blocks of rows and sometimes multiple rows not in order) which sometimes is based on a cell being empty, a cell being a set value/not a set value or being any value (not blank) Any suggestions for improvements? Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("E27") If Not Intersect(rng, Target) Is Nothing Then Rows(31).EntireRow.Hidden = IsEmpty(rng.Value) Rows(39).EntireRow.Hidden = IsEmpty(rng.Value) End If Select Case UCase(Range("B6").Value) Case "PBO" [35:36].EntireRow.Hidden = True [47:48].EntireRow.Hidden = True Case Else [35:36].EntireRow.Hidden = False [47:48].EntireRow.Hidden = False End Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide empty rows on open | Excel Discussion (Misc queries) | |||
Hide Empty Rows When Printing | Excel Programming | |||
Format to hide empty rows | Excel Discussion (Misc queries) | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions | |||
Code to hide rows based on cell contents | Excel Programming |