+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    ivory_kitten
    Guest

    [SOLVED] 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. #2
    Greg Wilson
    Guest

    RE: 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. #3
    ivory_kitten
    Guest

    RE: 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. #4
    Greg Wilson
    Guest

    RE: 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. #5
    ivory_kitten
    Guest

    RE: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1