simple code hide/show rows with cell = empty, set value or any val

I

ivory_kitten

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
 
G

Greg Wilson

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
 
I

ivory_kitten

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Greg Wilson

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
I

ivory_kitten

why doesn't my hide/unhiding work when i protect the sheet?

Greg Wilson said:
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 said:
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 said:
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



:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

create a loop so i don't have to keep retyping? 4
conditional row hide 2
when certain rows are hidden, can I also hide a combobox? 6
Hide Next row based on Cell Value 3
Unhide/hide based on drop down list 0
VBA code doesn't run when value selected from list 7
Hide rows using VBA based on cell value 0
macro 2

Top