conditional row hide


G

Guest

I have the following:


Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
'Conditions & Ranges'
Dim rng As Range, rng2 As Range, rng3 As Range
Dim wf As WorksheetFunction
Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As Boolean

Set wf = Application.WorksheetFunction
Set rng = Me.Range("E31") 'Option 2'
Set rng2 = Me.Range("B7") 'Packing Type'
Set rng3 = Me.Range("D2") 'Job Type
cond1 = (UCase(rng3.Value) = "Corporate")
cond2 = (UCase(rng3.Value) = "Private")
cond3 = (UCase(rng2.Value) = "PBR")
cond4 = (UCase(rng2.Value) = "PBO")

'Hides Packing Prices'
[37:37, 49:49].EntireRow.Hidden = wf.And(cond2, cond4)
[36:36, 48:48].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3),
wf.And(cond2, cond4))

'Hides depending on Type'
[8:8, 41:43].EntireRow.Hidden = (rng3.Value = "Private")
[9:20, 34:34, 38:40, 46:46].EntireRow.Hidden = (rng3.Value = "Corporate")
'Hides Option 2'
If Not Intersect(rng, target) Is Nothing Then
[35:35, 47:47].EntireRow.Hidden = IsEmpty(rng.Value)
End If
Application.ScreenUpdating = True
End Sub

All the hiding parts work, except for the first block "Hide Packing Prices",
no rows are hiding!?

What I am trying to do is have row 36 and row 48 hide when D2 = "Corporate"
& B7 = "PBR" and also hide those 2 rows again when D2 = "Private" & B7 =
"PBO" and hide row 37 and row 49 as well with the second one.

What am I doing wrong?
 
Ad

Advertisements

D

Doug Glancy

ivory_kitten,

Ucase converts all letters in a string to upper case, so Ucase of no string
will ever be "Corporate." In the immediate window:

? UCase("Corporate")
CORPORATE

hth,

Doug
 
Ad

Advertisements

G

Guest

OMG i feel so stupid! LOL thanks alot!

Doug Glancy said:
ivory_kitten,

Ucase converts all letters in a string to upper case, so Ucase of no string
will ever be "Corporate." In the immediate window:

? UCase("Corporate")
CORPORATE

hth,

Doug


ivory_kitten said:
I have the following:


Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
'Conditions & Ranges'
Dim rng As Range, rng2 As Range, rng3 As Range
Dim wf As WorksheetFunction
Dim cond1 As Boolean, cond2 As Boolean, cond3 As Boolean, cond4 As
Boolean

Set wf = Application.WorksheetFunction
Set rng = Me.Range("E31") 'Option 2'
Set rng2 = Me.Range("B7") 'Packing Type'
Set rng3 = Me.Range("D2") 'Job Type
cond1 = (UCase(rng3.Value) = "Corporate")
cond2 = (UCase(rng3.Value) = "Private")
cond3 = (UCase(rng2.Value) = "PBR")
cond4 = (UCase(rng2.Value) = "PBO")

'Hides Packing Prices'
[37:37, 49:49].EntireRow.Hidden = wf.And(cond2, cond4)
[36:36, 48:48].EntireRow.Hidden = wf.Or(wf.And(cond1, cond3),
wf.And(cond2, cond4))

'Hides depending on Type'
[8:8, 41:43].EntireRow.Hidden = (rng3.Value = "Private")
[9:20, 34:34, 38:40, 46:46].EntireRow.Hidden = (rng3.Value =
"Corporate")
'Hides Option 2'
If Not Intersect(rng, target) Is Nothing Then
[35:35, 47:47].EntireRow.Hidden = IsEmpty(rng.Value)
End If
Application.ScreenUpdating = True
End Sub

All the hiding parts work, except for the first block "Hide Packing
Prices",
no rows are hiding!?

What I am trying to do is have row 36 and row 48 hide when D2 =
"Corporate"
& B7 = "PBR" and also hide those 2 rows again when D2 = "Private" & B7 =
"PBO" and hide row 37 and row 49 as well with the second one.

What am I doing wrong?
 

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

Top