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

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

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

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

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