Announcement

Collapse
No announcement yet.

If statements that hide rows on change

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • If statements that hide rows on change



    I did a couple of if statements that hide some rows based on certain condition but when i now put another code that hides a larger portion of the sheet which covers the earlier ones . it stops working
    Below is my code. when i added the last part (boldened below) other hide procedures stopped working.
    kindly let me know what am doing wrong


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    '
    If Range("BA16").Value < 1 Then Exit Sub
    '
    'HIDE SOME ROWS BASED ON NUMBER OF ACCOUNTS BEING OPENNED
        If Range("AH22") = 1 Then
            Rows("29:36").EntireRow.Hidden = False
            Rows("29:36").EntireRow.Hidden = True
        ElseIf Range("AH22") = 2 Then
            Rows("29:36").EntireRow.Hidden = False
            Rows("31:36").EntireRow.Hidden = True
        ElseIf Range("AH22") = 3 Then
            Rows("29:36").EntireRow.Hidden = False
            Rows("33:36").EntireRow.Hidden = True
        ElseIf Range("AH22") = 4 Then
            Rows("29:36").EntireRow.Hidden = False
            Rows("35:36").EntireRow.Hidden = True
        ElseIf Range("AH22") = 5 Then
            Rows("29:36").EntireRow.Hidden = False
        ElseIf Range("AH22") = "" Then
            Rows("29:36").EntireRow.Hidden = False
        Else
            Rows("29:36").EntireRow.Hidden = False
        End If
    '
    '
    'SECTOR OF ACTIVITY CLEAR ON CHANGE
    If Not Intersect(Target, Range("I39:AH39")) Is Nothing Then
        Range("J41:AH41").Cells.ClearContents
        Range("J43:AH43").Cells.ClearContents
        Range("J45:Z45").Cells.ClearContents
    ElseIf Not Intersect(Target, Range("J41:AH41")) Is Nothing Then
        Range("J43:AH43").Cells.ClearContents
        Range("J45:Z45").Cells.ClearContents
    End If
    
    'VALUE CLEAR ON CHANGE
    If Not Intersect(Target, Range("J45:Z45")) Is Nothing Then
        Range("AD45:AI45").Cells.ClearContents
        Range("K47:AA47").Cells.ClearContents
    ElseIf Not Intersect(Target, Range("AD45:AI45")) Is Nothing Then
        Range("K47:AA47").Cells.ClearContents
    End If
    '
    '
    '
    'HIDE ROWS FOR ULTIMATE BENEFICIARY WHEN ITS NOT CORPRATE SEGMEMT IS SELECTED
    If Range("M24") <> "CONSUMER BANKING" Then
            Rows("106:123").EntireRow.Hidden = False
    Else
            Rows("106:123").EntireRow.Hidden = True
    End If
    '
    'HIDE RM SUBJECTIVE INFORMATION FIELD RECORD WHEN IT IS NOT HIGH
    If Range("I54") = "HIGH" Then
            Rows("124:129").EntireRow.Hidden = False
    Else
            Rows("124:129").EntireRow.Hidden = True
    End If
    '
    'HIDE ROWS FOR PEP BY ssociation
    If Range("AE161") = "MAIN PEP" Then
            Rows("180:187").EntireRow.Hidden = True
    Else
            Rows("180:187").EntireRow.Hidden = False
    End If
    '
    '
    'HIDDING THE SHEETS FOR PRINNTING PURPOSES
    '
        If Range("BB20") = 1 Then
            Rows("1:199").EntireRow.Hidden = False
            Rows("65:199").EntireRow.Hidden = True
        ElseIf Range("BB20") = 2 Then
            Rows("1:199").EntireRow.Hidden = False
            Rows("65:71").EntireRow.Hidden = True
            Rows("149:199").EntireRow.Hidden = True
         ElseIf Range("BB20") = 3 Then
            Rows("1:199").EntireRow.Hidden = False
            Rows("65:71").EntireRow.Hidden = True
            Rows("149:154").EntireRow.Hidden = True
        Else
           Rows("1:199").EntireRow.Hidden = False
            Rows("65:199").EntireRow.Hidden = True    
        End If
    '
    
       '
    End Sub

  • #2


    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Application.ScreenUpdating = False
        
        If Sh.Range("BA16").Value < 1 Then Exit Sub
    
        'HIDE SOME ROWS BASED ON NUMBER OF ACCOUNTS BEING OPENNED
        Sh.Rows("29:36").EntireRow.Hidden = False
        Select Case Sh.Range("AH22").Value
            Case 1
                Sh.Rows("29:36").EntireRow.Hidden = True
            Case 2
                Sh.Rows("31:36").EntireRow.Hidden = True
            Case 3
                Sh.Rows("33:36").EntireRow.Hidden = True
            Case 4
                Sh.Rows("35:36").EntireRow.Hidden = True
            Case Else
                'Do nothing for now
        End Select
        
        'SECTOR OF ACTIVITY CLEAR ON CHANGE
        If Not Intersect(Target, Sh.Range("I39:AH39")) Is Nothing Then
            Sh.Range("J41:AH41,J43:AH43,J45:Z45").ClearContents
        ElseIf Not Intersect(Target, Sh.Range("J41:AH41")) Is Nothing Then
            Sh.Range("J43:AH43,J45:Z45").ClearContents
        End If
        
        'VALUE CLEAR ON CHANGE
        If Not Intersect(Target, Sh.Range("J45:Z45")) Is Nothing Then
            Sh.Range("AD45:AI45,K47:AA47").ClearContents
        ElseIf Not Intersect(Target, Sh.Range("AD45:AI45")) Is Nothing Then
            Sh.Range("K47:AA47").ClearContents
        End If
        
        'HIDE ROWS FOR ULTIMATE BENEFICIARY WHEN ITS NOT CORPRATE SEGMEMT IS SELECTED
        Sh.Rows("106:123").EntireRow.Hidden = (Sh.Range("M24").Value = "CONSUMER BANKING")
        
        'HIDE RM SUBJECTIVE INFORMATION FIELD RECORD WHEN IT IS NOT HIGH
        Sh.Rows("124:129").EntireRow.Hidden = (Sh.Range("I54").Value <> "HIGH")
    
        'HIDE ROWS FOR PEP BY association
        Sh.Rows("180:187").EntireRow.Hidden = (Sh.Range("AE161") = "MAIN PEP")
        
        Application.ScreenUpdating = True
    End Sub
    
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        Cancel = True
        Application.ScreenUpdating = False
        Rows("1:199").EntireRow.Hidden = False
        Select Case Sh.Range("BB20").Value
            Case 2
                Sh.Rows("65:71").EntireRow.Hidden = True
                Sh.Rows("149:199").EntireRow.Hidden = True
            Case 3
                Sh.Rows("65:71").EntireRow.Hidden = True
                Sh.Rows("149:154").EntireRow.Hidden = True
            Case Else
                Sh.Rows("65:199").EntireRow.Hidden = True
        End Select
        Rows("1:199").EntireRow.Hidden = False
        Application.EnableEvents = False
        ActiveSheet.PrintOut
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub

    Comment

    Working...
    X