URGENT help needed with VBA coding

N

noeler1983

New Member
Joined
Mar 2, 2017
Messages
1
Hi All,

Total coding first timer and need some urgent help.

I've written a load of code (badly) for a worksheet so that rows and worksheets are hidden/unhidden in the workbook based on the values entered into cells.

However I'm getting a 'procedure too large' error message. I think i have to separate it into different sub-routines, or split into different modules, however i have no idea how to do this.

Grateful for any help anyone can give me 9please bare in mind i's completely new to this, can't speak the 'language' and am not very technical anyway.

I'm using excel 2016, but will need to be used in various excel versions.

Here is the code i currently have:

Code: 
 Private Sub Worksheet_Change(ByVal Target As Range)
  'Cumulative Impacts in Corporate Summary For Phased Developments'
    If Range("C22").Value = "Select:" Then
        Worksheets("Corporate Summary").Range("A16:A19").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A36:A48").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A215:A244").EntireRow.Hidden = True
    ElseIf Range("C22").Value = "Yes" Then
        Worksheets("Corporate Summary").Range("A16:A19").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A36:A48").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A215:A244").EntireRow.Hidden = False
    ElseIf Range("C22").Value = "No" Then
        Worksheets("Corporate Summary").Range("A16:A19").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A36:A48").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A215:A244").EntireRow.Hidden = True
     
    End If
    'Unhides 'other' Application Type Details'
    If Range("C24").Value = "Select:" Then
        Rows("25").EntireRow.Hidden = True
    ElseIf Range("C24").Value = "Outline" Then
        Rows("25").EntireRow.Hidden = True
    ElseIf Range("C24").Value = "Full" Then
        Rows("25").EntireRow.Hidden = True
    ElseIf Range("C24").Value = "Hybrid" Then
        Rows("25").EntireRow.Hidden = True
    ElseIf Range("C24").Value = "Reserved Matters" Then
        Rows("25").EntireRow.Hidden = True
    ElseIf Range("C24").Value = "Other" Then
        Rows("25").EntireRow.Hidden = False
     
    End If
    'Unhides Phase Specific Rows in Corporate Summary and Phase Specific Calculator Tabs'
    If Range("G24").Value = "Select:" Then
        Worksheets("Corporate Summary").Range("A52:A78").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A79:A213").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A251").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A252:A256").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A261").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A262:A266").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A270:A282").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A283:A295").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A296:A308").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A309:A321").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A322:A334").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A335:A347").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A350:A362").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A363:A375").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A376:A388").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A389:A401").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A402:A414").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A415:A427").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A430:A442").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A443:A456").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A457:A469").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A470:A482").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A483:A495").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A496:A507").EntireRow.Hidden = True
        Worksheets("Habitat Calculator Phase 1").Visible = False
        Worksheets("Habitat Calculator Phase 2").Visible = False
        Worksheets("Habitat Calculator Phase 3").Visible = False
        Worksheets("Habitat Calculator Phase 4").Visible = False
        Worksheets("Habitat Calculator Phase 5").Visible = False
        Worksheets("Linear Calculator Phase 1").Visible = False
        Worksheets("Linear Calculator Phase 2").Visible = False
        Worksheets("Linear Calculator Phase 3").Visible = False
        Worksheets("Linear Calculator Phase 4").Visible = False
        Worksheets("Linear Calculator Phase 5").Visible = False
    ElseIf Range("G24").Value = "Whole Site / Hybrid" Then
        Worksheets("Corporate Summary").Range("A52:A78").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A79:A213").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A251").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A252:A256").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A261").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A262:A266").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A270:A282").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A283:A295").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A296:A308").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A309:A321").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A322:A334").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A335:A347").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A350:A362").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A363:A375").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A376:A388").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A389:A401").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A402:A414").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A415:A427").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A430:A442").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A443:A456").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A457:A469").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A470:A482").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A483:A495").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A496:A507").EntireRow.Hidden = True
        Worksheets("Habitat Calculator Phase 1").Visible = False
        Worksheets("Habitat Calculator Phase 2").Visible = False
        Worksheets("Habitat Calculator Phase 3").Visible = False
        Worksheets("Habitat Calculator Phase 4").Visible = False
        Worksheets("Habitat Calculator Phase 5").Visible = False
        Worksheets("Linear Calculator Phase 1").Visible = False
        Worksheets("Linear Calculator Phase 2").Visible = False
        Worksheets("Linear Calculator Phase 3").Visible = False
        Worksheets("Linear Calculator Phase 4").Visible = False
        Worksheets("Linear Calculator Phase 5").Visible = False
    ElseIf Range("G24").Value = "1" Then
        Worksheets("Corporate Summary").Range("A52:A78").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A79:A105").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A106:A213").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A251").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A252").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A253:A256").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A261").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A262").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A263:A266").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A270:A282").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A283:A295").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A296:A308").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A309:A321").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A322:A334").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A335:A347").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A350:A362").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A363:A375").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A376:A388").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A389:A401").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A402:A414").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A415:A427").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A430:A442").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A443:A455").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A456:A468").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A469:A481").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A482:A494").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A495:A507").EntireRow.Hidden = True
        Worksheets("Habitat Calculator Phase 1").Visible = True
        Worksheets("Habitat Calculator Phase 2").Visible = False
        Worksheets("Habitat Calculator Phase 3").Visible = False
        Worksheets("Habitat Calculator Phase 4").Visible = False
        Worksheets("Habitat Calculator Phase 5").Visible = False
        Worksheets("Linear Calculator Phase 1").Visible = True
        Worksheets("Linear Calculator Phase 2").Visible = False
        Worksheets("Linear Calculator Phase 3").Visible = False
        Worksheets("Linear Calculator Phase 4").Visible = False
        Worksheets("Linear Calculator Phase 5").Visible = False
    ElseIf Range("G24").Value = "2" Then
        Worksheets("Corporate Summary").Range("A52:A105").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A106:A132").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A133:A213").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A251:A252").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A253").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A254:A256").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A261:A262").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A263").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A264:A266").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A270:A282").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A283:A295").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A296:A308").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A309:A321").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A322:A334").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A335:A347").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A350:A362").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A363:A375").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A376:A388").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A389:A401").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A402:A414").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A415:A427").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A430:A442").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A443:A455").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A456:A468").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A469:A481").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A482:A494").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A495:A507").EntireRow.Hidden = True
        Worksheets("Habitat Calculator Phase 1").Visible = False
        Worksheets("Habitat Calculator Phase 2").Visible = True
        Worksheets("Habitat Calculator Phase 3").Visible = False
        Worksheets("Habitat Calculator Phase 4").Visible = False
        Worksheets("Habitat Calculator Phase 5").Visible = False
        Worksheets("Linear Calculator Phase 1").Visible = False
        Worksheets("Linear Calculator Phase 2").Visible = True
        Worksheets("Linear Calculator Phase 3").Visible = False
        Worksheets("Linear Calculator Phase 4").Visible = False
        Worksheets("Linear Calculator Phase 5").Visible = False
    ElseIf Range("G24").Value = "3" Then
        Worksheets("Corporate Summary").Range("A52:A132").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A133:A159").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A160:A213").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A251:A253").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A254").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A255:A256").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A261:A263").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A264").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A265:A266").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A270:A282").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A283:A295").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A296:A308").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A309:A321").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A322:A334").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A335:A347").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A350:A362").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A363:A375").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A376:A388").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A389:A401").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A402:A414").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A415:A427").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A430:A442").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A443:A455").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A456:A468").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A469:A481").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A482:A494").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A495:A507").EntireRow.Hidden = True
        Worksheets("Habitat Calculator Phase 1").Visible = False
        Worksheets("Habitat Calculator Phase 2").Visible = False
        Worksheets("Habitat Calculator Phase 3").Visible = True
        Worksheets("Habitat Calculator Phase 4").Visible = False
        Worksheets("Habitat Calculator Phase 5").Visible = False
        Worksheets("Linear Calculator Phase 1").Visible = False
        Worksheets("Linear Calculator Phase 2").Visible = False
        Worksheets("Linear Calculator Phase 3").Visible = True
        Worksheets("Linear Calculator Phase 4").Visible = False
        Worksheets("Linear Calculator Phase 5").Visible = False
    ElseIf Range("G24").Value = "4" Then
        Worksheets("Corporate Summary").Range("A52:A159").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A160:A186").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A187:A213").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A251:A254").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A255").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A256").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A261:A264").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A265").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A266").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A270:A282").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A283:A295").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A296:A308").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A309:A321").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A322:A334").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A335:A347").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A350:A362").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A363:A375").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A376:A388").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A389:A401").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A402:A414").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A415:A427").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A430:A442").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A443:A455").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A456:A468").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A469:A481").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A482:A494").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A495:A507").EntireRow.Hidden = True
        Worksheets("Habitat Calculator Phase 1").Visible = False
        Worksheets("Habitat Calculator Phase 2").Visible = False
        Worksheets("Habitat Calculator Phase 3").Visible = False
        Worksheets("Habitat Calculator Phase 4").Visible = True
        Worksheets("Habitat Calculator Phase 5").Visible = False
        Worksheets("Linear Calculator Phase 1").Visible = False
        Worksheets("Linear Calculator Phase 2").Visible = False
        Worksheets("Linear Calculator Phase 3").Visible = False
        Worksheets("Linear Calculator Phase 4").Visible = True
        Worksheets("Linear Calculator Phase 5").Visible = False
    ElseIf Range("G24").Value = "5" Then
        Worksheets("Corporate Summary").Range("A52:A186").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A187:A213").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A251:A255").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A256").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A261:A265").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A266").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A270:A282").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A283:A295").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A296:A308").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A309:A321").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A322:A334").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A335:A347").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A350:A362").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A363:A375").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A376:A388").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A389:A401").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A402:A414").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A415:A427").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A430:A442").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A443:A455").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A456:A468").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A469:A481").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A482:A494").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A495:A507").EntireRow.Hidden = False
        Worksheets("Habitat Calculator Phase 1").Visible = False
        Worksheets("Habitat Calculator Phase 2").Visible = False
        Worksheets("Habitat Calculator Phase 3").Visible = False
        Worksheets("Habitat Calculator Phase 4").Visible = False
        Worksheets("Habitat Calculator Phase 5").Visible = True
        Worksheets("Linear Calculator Phase 1").Visible = False
        Worksheets("Linear Calculator Phase 2").Visible = False
        Worksheets("Linear Calculator Phase 3").Visible = False
        Worksheets("Linear Calculator Phase 4").Visible = False
        Worksheets("Linear Calculator Phase 5").Visible = True
        
    End If
    'Unhides BREEAM Summary Tabs in Project Management and BREEAM Summary Tab'
    If Range("C40").Value = "No" Then
        Rows("427:437").EntireRow.Hidden = True
        Worksheets("BREEAM Summary").Visible = False
    ElseIf Range("C40").Value = "Yes" Then
        Rows("427:437").EntireRow.Hidden = False
        Worksheets("BREEAM Summary").Visible = True
    ElseIf Range("C40").Value = "Select:" Then
        Rows("427:437").EntireRow.Hidden = True
        Worksheets("BREEAM Summary").Visible = False    End If
    'Designated Sites Impacted Upon'
    If Range("C48").Value = "Select:" Then
        Rows("49:54").EntireRow.Hidden = True
    ElseIf Range("C48").Value = "No Impact" Then
        Rows("49:54").EntireRow.Hidden = True
    ElseIf Range("C48").Value = "1" Then
        Rows("49:50").EntireRow.Hidden = False
        Rows("51:54").EntireRow.Hidden = True
    ElseIf Range("C48").Value = "2" Then
        Rows("49:51").EntireRow.Hidden = False
        Rows("52:54").EntireRow.Hidden = True
    ElseIf Range("C48").Value = "3" Then
        Rows("49:52").EntireRow.Hidden = False
        Rows("53:54").EntireRow.Hidden = True
    ElseIf Range("C48").Value = "4" Then
        Rows("49:53").EntireRow.Hidden = False
        Rows("54").EntireRow.Hidden = True
    ElseIf Range("C48").Value = "5" Then
        Rows("489:54").EntireRow.Hidden = False
            
    End If
    'Ancient Woodland Impacted Upon'
    If Range("C56").Value = "Select:" Then
        Rows("57:62").EntireRow.Hidden = True
    ElseIf Range("C56").Value = "No Impact" Then
        Rows("57:62").EntireRow.Hidden = True
    ElseIf Range("C56").Value = "1" Then
        Rows("57:58").EntireRow.Hidden = False
        Rows("59:62").EntireRow.Hidden = True
    ElseIf Range("C56").Value = "2" Then
        Rows("57:59").EntireRow.Hidden = False
        Rows("60:62").EntireRow.Hidden = True
    ElseIf Range("C56").Value = "3" Then
        Rows("57:60").EntireRow.Hidden = False
        Rows("61:62").EntireRow.Hidden = True
    ElseIf Range("C56").Value = "4" Then
        Rows("57:61").EntireRow.Hidden = False
        Rows("62").EntireRow.Hidden = True
    ElseIf Range("C56").Value = "5" Then
        Rows("57:62").EntireRow.Hidden = False
        
    End If
    'Habitat Survey Recommendations and Whether in Date'
    If Range("C66").Value = "Select:" Then
        Rows("67:78").EntireRow.Hidden = True
        Rows("186:195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "None Required" Then
        Rows("67:78").EntireRow.Hidden = True
        Rows("186:195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "1" Then
        Rows("67:68").EntireRow.Hidden = False
        Rows("69:78").EntireRow.Hidden = True
        Rows("186").EntireRow.Hidden = False
        Rows("187:195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "2" Then
        Rows("67:69").EntireRow.Hidden = False
        Rows("70:78").EntireRow.Hidden = True
        Rows("186:187").EntireRow.Hidden = False
        Rows("188:195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "3" Then
        Rows("67:70").EntireRow.Hidden = False
        Rows("71:78").EntireRow.Hidden = True
        Rows("186:188").EntireRow.Hidden = False
        Rows("189:195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "4" Then
        Rows("67:71").EntireRow.Hidden = False
        Rows("72:78").EntireRow.Hidden = True
        Rows("186:189").EntireRow.Hidden = False
        Rows("190:195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "5" Then
        Rows("67:72").EntireRow.Hidden = False
        Rows("73:78").EntireRow.Hidden = True
        Rows("186:190").EntireRow.Hidden = False
        Rows("191:195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "6" Then
        Rows("67:73").EntireRow.Hidden = False
        Rows("74:78").EntireRow.Hidden = True
        Rows("186:191").EntireRow.Hidden = False
        Rows("192:195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "7" Then
        Rows("67:74").EntireRow.Hidden = False
        Rows("75:78").EntireRow.Hidden = True
        Rows("186:192").EntireRow.Hidden = False
        Rows("193:195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "8" Then
        Rows("67:75").EntireRow.Hidden = False
        Rows("76:78").EntireRow.Hidden = True
        Rows("186:193").EntireRow.Hidden = False
        Rows("194:195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "9" Then
        Rows("67:76").EntireRow.Hidden = False
        Rows("77").EntireRow.Hidden = True
        Rows("186:194").EntireRow.Hidden = False
        Rows("195").EntireRow.Hidden = True
    ElseIf Range("C66").Value = "10" Then
        Rows("67:78").EntireRow.Hidden = False
        Rows("186:195").EntireRow.Hidden = False
        
    End If
    'Species Surveys Recommendations and Whether in Date'
    If Range("C79").Value = "Select:" Then
        Rows("80:90").EntireRow.Hidden = True
        Rows("196:205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "None Required" Then
        Rows("80:90").EntireRow.Hidden = True
        Rows("196:205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "1" Then
        Rows("80:81").EntireRow.Hidden = False
        Rows("82:90").EntireRow.Hidden = True
        Rows("196").EntireRow.Hidden = False
        Rows("197:205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "2" Then
        Rows("80:82").EntireRow.Hidden = False
        Rows("83:90").EntireRow.Hidden = True
        Rows("196:197").EntireRow.Hidden = False
        Rows("198:205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "3" Then
        Rows("80:83").EntireRow.Hidden = False
        Rows("84:90").EntireRow.Hidden = True
        Rows("196:198").EntireRow.Hidden = False
        Rows("199:205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "4" Then
        Rows("80:84").EntireRow.Hidden = False
        Rows("85:90").EntireRow.Hidden = True
        Rows("196:199").EntireRow.Hidden = False
        Rows("200:205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "5" Then
        Rows("80:85").EntireRow.Hidden = False
        Rows("86:90").EntireRow.Hidden = True
        Rows("196:200").EntireRow.Hidden = False
        Rows("201:205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "6" Then
        Rows("80:86").EntireRow.Hidden = False
        Rows("87:90").EntireRow.Hidden = True
        Rows("196:201").EntireRow.Hidden = False
        Rows("202:205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "7" Then
        Rows("80:87").EntireRow.Hidden = False
        Rows("88:90").EntireRow.Hidden = True
        Rows("196:202").EntireRow.Hidden = False
        Rows("203:205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "8" Then
        Rows("80:88").EntireRow.Hidden = False
        Rows("89:90").EntireRow.Hidden = True
        Rows("196:203").EntireRow.Hidden = False
        Rows("204:205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "9" Then
        Rows("80:89").EntireRow.Hidden = False
        Rows("90").EntireRow.Hidden = True
        Rows("196:204").EntireRow.Hidden = False
        Rows("205").EntireRow.Hidden = True
    ElseIf Range("C79").Value = "10" Then
        Rows("80:90").EntireRow.Hidden = False
        Rows("196:205").EntireRow.Hidden = False
   
    End If
    'Additional Assessments Required to Supoort Planning and Whether in Date'
    If Range("C92").Value = "Select:" Then
        Rows("93:103").EntireRow.Hidden = True
        Rows("206:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "None Required" Then
        Rows("93:103").EntireRow.Hidden = True
        Rows("206:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "1" Then
        Rows("93:94").EntireRow.Hidden = False
        Rows("95:103").EntireRow.Hidden = True
        Rows("206").EntireRow.Hidden = False
        Rows("207:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "2" Then
        Rows("93:95").EntireRow.Hidden = False
        Rows("96:103").EntireRow.Hidden = True
        Rows("206:207").EntireRow.Hidden = False
        Rows("208:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "3" Then
        Rows("93:96").EntireRow.Hidden = False
        Rows("97:103").EntireRow.Hidden = True
        Rows("206:208").EntireRow.Hidden = False
        Rows("209:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "4" Then
        Rows("93:97").EntireRow.Hidden = False
        Rows("98:103").EntireRow.Hidden = True
        Rows("206:209").EntireRow.Hidden = False
        Rows("2010:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "5" Then
        Rows("93:98").EntireRow.Hidden = False
        Rows("99:103").EntireRow.Hidden = True
        Rows("206:210").EntireRow.Hidden = False
        Rows("211:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "6" Then
        Rows("93:99").EntireRow.Hidden = False
        Rows("100:103").EntireRow.Hidden = True
        Rows("206:211").EntireRow.Hidden = False
        Rows("212:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "7" Then
        Rows("93:100").EntireRow.Hidden = False
        Rows("101:103").EntireRow.Hidden = True
        Rows("206:212").EntireRow.Hidden = False
        Rows("213:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "8" Then
        Rows("93:101").EntireRow.Hidden = False
        Rows("102:103").EntireRow.Hidden = True
        Rows("206:213").EntireRow.Hidden = False
        Rows("214:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "9" Then
        Rows("93:102").EntireRow.Hidden = False
        Rows("103").EntireRow.Hidden = True
        Rows("206:214").EntireRow.Hidden = False
        Rows("215:216").EntireRow.Hidden = True
    ElseIf Range("C92").Value = "10" Then
        Rows("93:103").EntireRow.Hidden = False
        Rows("206:216").EntireRow.Hidden = False
  
    End If
    'Potential/Confirmed Protected Habitats'
    If Range("C105").Value = "Select:" Then
        Rows("106:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "None Present" Then
        Rows("106:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "1" Then
        Rows("106:110").EntireRow.Hidden = False
        Rows("111:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "2" Then
        Rows("106:110").EntireRow.Hidden = False
        Rows("111:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "3" Then
        Rows("106:110").EntireRow.Hidden = False
        Rows("111:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "4" Then
        Rows("106:110").EntireRow.Hidden = False
        Rows("111:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "5" Then
        Rows("106:111").EntireRow.Hidden = False
        Rows("112:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "6" Then
        Rows("106:112").EntireRow.Hidden = False
        Rows("113:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "7" Then
        Rows("106:113").EntireRow.Hidden = False
        Rows("114:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "8" Then
        Rows("106:114").EntireRow.Hidden = False
        Rows("115:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "9" Then
        Rows("106:115").EntireRow.Hidden = False
        Rows("116:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "10" Then
        Rows("106:116").EntireRow.Hidden = False
        Rows("117:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "11" Then
        Rows("106:117").EntireRow.Hidden = False
        Rows("118:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "12" Then
        Rows("106:118").EntireRow.Hidden = False
        Rows("119:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "13" Then
        Rows("106:119").EntireRow.Hidden = False
        Rows("120:122").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "14" Then
        Rows("106:120").EntireRow.Hidden = False
        Rows("121").EntireRow.Hidden = True
    ElseIf Range("C105").Value = "15" Then
        Rows("106:122").EntireRow.Hidden = False
        
    End If
    'Potential Confirmed Protected Species'
    If Range("C123").Value = "Select:" Then
        Rows("124:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "None Present" Then
        Rows("124:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "1" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "2" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "3" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "4" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "5" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "6" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "7" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "8" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "9" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "10" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "11" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "12" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "13" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139:140").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "14" Then
        Rows("124:138").EntireRow.Hidden = False
        Rows("139").EntireRow.Hidden = True
    ElseIf Range("C123").Value = "15" Then
        Rows("124:140").EntireRow.Hidden = False
        
    End If
    'Potential Confirmed Notable Habitats'
    If Range("C141").Value = "Select:" Then
        Rows("142:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "None Present" Then
        Rows("142:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "1" Then
        Rows("142:143").EntireRow.Hidden = False
        Rows("144:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "2" Then
        Rows("142:144").EntireRow.Hidden = False
        Rows("145:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "3" Then
        Rows("142:145").EntireRow.Hidden = False
        Rows("146:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "4" Then
        Rows("142:146").EntireRow.Hidden = False
        Rows("147:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "5" Then
        Rows("142:147").EntireRow.Hidden = False
        Rows("148:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "6" Then
        Rows("142:148").EntireRow.Hidden = False
        Rows("149:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "7" Then
        Rows("142:149").EntireRow.Hidden = False
        Rows("150:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "8" Then
        Rows("142:150").EntireRow.Hidden = False
        Rows("151:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "9" Then
        Rows("142:151").EntireRow.Hidden = False
        Rows("152:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "10" Then
        Rows("142:152").EntireRow.Hidden = False
        Rows("153:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "11" Then
        Rows("142:153").EntireRow.Hidden = False
        Rows("154:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "12" Then
        Rows("142:154").EntireRow.Hidden = False
        Rows("155:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "13" Then
        Rows("142:155").EntireRow.Hidden = False
        Rows("156:158").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "14" Then
        Rows("142:156").EntireRow.Hidden = False
        Rows("157").EntireRow.Hidden = True
    ElseIf Range("C141").Value = "15" Then
        Rows("142:158").EntireRow.Hidden = False
        
    End If
    'Potential Confirmed Notable Species'
    If Range("C159").Value = "Select:" Then
        Rows("160:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "None Present" Then
        Rows("160:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "1" Then
        Rows("160:161").EntireRow.Hidden = False
        Rows("162:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "2" Then
        Rows("160:162").EntireRow.Hidden = False
        Rows("163:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "3" Then
        Rows("160:163").EntireRow.Hidden = False
        Rows("164:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "4" Then
        Rows("160:164").EntireRow.Hidden = False
        Rows("165:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "5" Then
        Rows("160:165").EntireRow.Hidden = False
        Rows("166:176").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "6" Then
        Rows("160:166").EntireRow.Hidden = False
        Rows("167:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "7" Then
        Rows("160:167").EntireRow.Hidden = False
        Rows("168:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "8" Then
        Rows("160:168").EntireRow.Hidden = False
        Rows("169:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "9" Then
        Rows("160:169").EntireRow.Hidden = False
        Rows("170:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "10" Then
        Rows("160:170").EntireRow.Hidden = False
        Rows("171:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "11" Then
        Rows("160:171").EntireRow.Hidden = False
        Rows("172:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "12" Then
        Rows("160:172").EntireRow.Hidden = False
        Rows("173:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "13" Then
        Rows("160:173").EntireRow.Hidden = False
        Rows("174:175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "14" Then
        Rows("160:174").EntireRow.Hidden = False
        Rows("175").EntireRow.Hidden = True
    ElseIf Range("C159").Value = "15" Then
        Rows("160:175").EntireRow.Hidden = False
    End If
    'Invasive Species'
    If Range("C177").Value = "Select:" Then
        Rows("178:183").EntireRow.Hidden = True
    ElseIf Range("C177").Value = "None Present" Then
        Rows("178:183").EntireRow.Hidden = True
    ElseIf Range("C177").Value = "1" Then
        Rows("178:179").EntireRow.Hidden = False
        Rows("180:181").EntireRow.Hidden = True
    ElseIf Range("C177").Value = "2" Then
        Rows("178:180").EntireRow.Hidden = False
        Rows("181:183").EntireRow.Hidden = True
    ElseIf Range("C177").Value = "3" Then
        Rows("178:181").EntireRow.Hidden = False
        Rows("182:183").EntireRow.Hidden = True
    ElseIf Range("C177").Value = "4" Then
        Rows("178:182").EntireRow.Hidden = False
        Rows("183").EntireRow.Hidden = True
    ElseIf Range("C177").Value = "5" Then
        Rows("178:183").EntireRow.Hidden = False
                      
    End If
    'Planning Conditions'
    If Range("C221").Value = "Select:" Then
        Rows("222:227").EntireRow.Hidden = True
    ElseIf Range("C221").Value = "Awaiting" Then
        Rows("222:227").EntireRow.Hidden = True
    ElseIf Range("C221").Value = "None Required" Then
        Rows("222:227").EntireRow.Hidden = True
    ElseIf Range("C221").Value = "1" Then
        Rows("222:223").EntireRow.Hidden = False
        Rows("224:227").EntireRow.Hidden = True
    ElseIf Range("C221").Value = "2" Then
        Rows("222:224").EntireRow.Hidden = False
        Rows("225:227").EntireRow.Hidden = True
    ElseIf Range("C221").Value = "3" Then
        Rows("222:225").EntireRow.Hidden = False
        Rows("226:227").EntireRow.Hidden = True
    ElseIf Range("C221").Value = "4" Then
        Rows("222:226").EntireRow.Hidden = False
        Rows("227").EntireRow.Hidden = True
    ElseIf Range("C221").Value = "5" Then
        Rows("222:227").EntireRow.Hidden = False
        
    End If
    'Natural England Licences and Works / Monitoring Info'
    If Range("C229").Value = "Select:" Then
        Rows("230:235").EntireRow.Hidden = True
        Rows("356:364").EntireRow.Hidden = True
    ElseIf Range("C229").Value = "None Required" Then
        Rows("230:235").EntireRow.Hidden = True
        Rows("356:364").EntireRow.Hidden = True
    ElseIf Range("C229").Value = "1" Then
        Rows("230:231").EntireRow.Hidden = False
        Rows("232:235").EntireRow.Hidden = True
        Rows("356:358").EntireRow.Hidden = False
        Rows("359:364").EntireRow.Hidden = True
    ElseIf Range("C229").Value = "2" Then
        Rows("230:232").EntireRow.Hidden = False
        Rows("233:235").EntireRow.Hidden = True
        Rows("356:359").EntireRow.Hidden = False
        Rows("360:364").EntireRow.Hidden = True
    ElseIf Range("C229").Value = "3" Then
        Rows("230:233").EntireRow.Hidden = False
        Rows("234:235").EntireRow.Hidden = True
        Rows("356:360").EntireRow.Hidden = False
        Rows("361:364").EntireRow.Hidden = True
    ElseIf Range("C229").Value = "4" Then
        Rows("230:234").EntireRow.Hidden = False
        Rows("235").EntireRow.Hidden = True
        Rows("356:361").EntireRow.Hidden = False
        Rows("362:364").EntireRow.Hidden = True
    ElseIf Range("C229").Value = "5" Then
        Rows("230:235").EntireRow.Hidden = False
        Rows("356:364").EntireRow.Hidden = False
        
    End If
    'Habitat Protection Recommendations'
    If Range("C237").Value = "Select:" Then
        Rows("238:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "None Required" Then
        Rows("238:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "1" Then
        Rows("238:239").EntireRow.Hidden = False
        Rows("240:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "2" Then
        Rows("238:240").EntireRow.Hidden = False
        Rows("241:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "3" Then
        Rows("238:241").EntireRow.Hidden = False
        Rows("242:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "4" Then
        Rows("238:242").EntireRow.Hidden = False
        Rows("243:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "5" Then
        Rows("238:243").EntireRow.Hidden = False
        Rows("244:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "6" Then
        Rows("238:244").EntireRow.Hidden = False
        Rows("245:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "7" Then
        Rows("238:245").EntireRow.Hidden = False
        Rows("246:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "8" Then
        Rows("238:246").EntireRow.Hidden = False
        Rows("247:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "9" Then
        Rows("238:247").EntireRow.Hidden = False
        Rows("248:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "10" Then
        Rows("238:248").EntireRow.Hidden = False
        Rows("249:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "11" Then
        Rows("238:249").EntireRow.Hidden = False
        Rows("250:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "12" Then
        Rows("238:250").EntireRow.Hidden = False
        Rows("251:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "13" Then
        Rows("238:251").EntireRow.Hidden = False
        Rows("252:254").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "14" Then
        Rows("238:252").EntireRow.Hidden = False
        Rows("253").EntireRow.Hidden = True
    ElseIf Range("C237").Value = "15" Then
        Rows("238:254").EntireRow.Hidden = False
        
    End If
    'Species Protection Recommendations'
    If Range("C255").Value = "Select:" Then
        Rows("256:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "None Required" Then
        Rows("256:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "1" Then
        Rows("256:257").EntireRow.Hidden = False
        Rows("258:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "2" Then
        Rows("256:258").EntireRow.Hidden = False
        Rows("259:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "3" Then
        Rows("256:259").EntireRow.Hidden = False
        Rows("260:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "4" Then
        Rows("256:260").EntireRow.Hidden = False
        Rows("261:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "5" Then
        Rows("256:261").EntireRow.Hidden = False
        Rows("262:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "6" Then
        Rows("256:262").EntireRow.Hidden = False
        Rows("263:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "7" Then
        Rows("256:263").EntireRow.Hidden = False
        Rows("264:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "8" Then
        Rows("256:264").EntireRow.Hidden = False
        Rows("265:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "9" Then
        Rows("256:265").EntireRow.Hidden = False
        Rows("266:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "10" Then
        Rows("256:266").EntireRow.Hidden = False
        Rows("267:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "11" Then
        Rows("256:267").EntireRow.Hidden = False
        Rows("268:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "12" Then
        Rows("256:268").EntireRow.Hidden = False
        Rows("269:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "13" Then
        Rows("256:269").EntireRow.Hidden = False
        Rows("270:271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "14" Then
        Rows("256:270").EntireRow.Hidden = False
        Rows("271").EntireRow.Hidden = True
    ElseIf Range("C255").Value = "15" Then
        Rows("256:271").EntireRow.Hidden = False
        
    End If
    'Habitat Mitigation/Enhancement Recommendations'
    If Range("C273").Value = "Select:" Then
        Rows("274:290").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "1" Then
        Rows("274:275").EntireRow.Hidden = False
        Rows("276:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "2" Then
        Rows("274:276").EntireRow.Hidden = False
        Rows("277:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "3" Then
        Rows("274:277").EntireRow.Hidden = False
        Rows("278:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "4" Then
        Rows("274:278").EntireRow.Hidden = False
        Rows("279:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "5" Then
        Rows("274:279").EntireRow.Hidden = False
        Rows("280:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "6" Then
        Rows("274:280").EntireRow.Hidden = False
        Rows("281:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "7" Then
        Rows("274:281").EntireRow.Hidden = False
        Rows("282:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "8" Then
        Rows("274:282").EntireRow.Hidden = False
        Rows("283:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "9" Then
        Rows("274:283").EntireRow.Hidden = False
        Rows("284:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "10" Then
        Rows("274:284").EntireRow.Hidden = False
        Rows("285:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "11" Then
        Rows("274:285").EntireRow.Hidden = False
        Rows("286:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "12" Then
        Rows("274:286").EntireRow.Hidden = False
        Rows("287:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "13" Then
        Rows("274:287").EntireRow.Hidden = False
        Rows("288:289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "14" Then
        Rows("274:288").EntireRow.Hidden = False
        Rows("289").EntireRow.Hidden = True
    ElseIf Range("C273").Value = "15" Then
        Rows("274:290").EntireRow.Hidden = False
    End If
    'Species Mitigation/Enhancement Recommendations'
    If Range("C291").Value = "Select:" Then
        Rows("292:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "1" Then
        Rows("292:293").EntireRow.Hidden = False
        Rows("294:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "2" Then
        Rows("292:294").EntireRow.Hidden = False
        Rows("295:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "3" Then
        Rows("292:295").EntireRow.Hidden = False
        Rows("296:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "4" Then
        Rows("292:296").EntireRow.Hidden = False
        Rows("297:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "5" Then
        Rows("292:297").EntireRow.Hidden = False
        Rows("298:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "6" Then
        Rows("292:298").EntireRow.Hidden = False
        Rows("299:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "7" Then
        Rows("292:299").EntireRow.Hidden = False
        Rows("300:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "8" Then
        Rows("292:300").EntireRow.Hidden = False
        Rows("301:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "9" Then
        Rows("292:301").EntireRow.Hidden = False
        Rows("302:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "10" Then
        Rows("292:302").EntireRow.Hidden = False
        Rows("303:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "11" Then
        Rows("292:303").EntireRow.Hidden = False
        Rows("304:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "12" Then
        Rows("292:304").EntireRow.Hidden = False
        Rows("305:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "13" Then
        Rows("292:305").EntireRow.Hidden = False
        Rows("306:307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "14" Then
        Rows("292:306").EntireRow.Hidden = False
        Rows("307").EntireRow.Hidden = True
    ElseIf Range("C291").Value = "15" Then
        Rows("292:307").EntireRow.Hidden = False
    End If
    'Habitat Connectivity Recommendations'
    If Range("C309").Value = "Select:" Then
        Rows("310:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "1" Then
        Rows("310:311").EntireRow.Hidden = False
        Rows("313:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "2" Then
        Rows("310:312").EntireRow.Hidden = False
        Rows("313:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "3" Then
        Rows("310:313").EntireRow.Hidden = False
        Rows("314:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "4" Then
        Rows("310:314").EntireRow.Hidden = False
        Rows("315:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "5" Then
        Rows("310:315").EntireRow.Hidden = False
        Rows("316:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "6" Then
        Rows("310:316").EntireRow.Hidden = False
        Rows("317:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "7" Then
        Rows("310:317").EntireRow.Hidden = False
        Rows("318:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "8" Then
        Rows("310:318").EntireRow.Hidden = False
        Rows("319:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "9" Then
        Rows("310:319").EntireRow.Hidden = False
        Rows("320:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "10" Then
        Rows("310:320").EntireRow.Hidden = False
        Rows("321:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "11" Then
        Rows("310:321").EntireRow.Hidden = False
        Rows("322:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "12" Then
        Rows("310:322").EntireRow.Hidden = False
        Rows("323:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "13" Then
        Rows("310:323").EntireRow.Hidden = False
        Rows("324:325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "14" Then
        Rows("310:325").EntireRow.Hidden = False
        Rows("325").EntireRow.Hidden = True
    ElseIf Range("C309").Value = "15" Then
        Rows("310:325").EntireRow.Hidden = False
    End If
    'Additional Design Considerations'
    If Range("C327").Value = "Select:" Then
        Rows("328:338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "None Required" Then
        Rows("328:338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "1" Then
        Rows("328:329").EntireRow.Hidden = False
        Rows("330:338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "2" Then
        Rows("328:330").EntireRow.Hidden = False
        Rows("331:338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "3" Then
        Rows("328:331").EntireRow.Hidden = False
        Rows("332:338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "4" Then
        Rows("328:332").EntireRow.Hidden = False
        Rows("333:338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "5" Then
        Rows("328:333").EntireRow.Hidden = False
        Rows("334:338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "6" Then
        Rows("328:334").EntireRow.Hidden = False
        Rows("335:338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "7" Then
        Rows("328:335").EntireRow.Hidden = False
        Rows("336:338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "8" Then
        Rows("328:336").EntireRow.Hidden = False
        Rows("337:338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "9" Then
        Rows("328:337").EntireRow.Hidden = False
        Rows("338").EntireRow.Hidden = True
    ElseIf Range("C327").Value = "10" Then
        Rows("328:338").EntireRow.Hidden = False
        
    End If
    'Timescale Constraints'
    If Range("C340").Value = "Select:" Then
        Rows("341:352").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "None" Then
        Rows("341:352").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "1" Then
        Rows("341:342").EntireRow.Hidden = False
        Rows("343:352").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "2" Then
        Rows("341:343").EntireRow.Hidden = False
        Rows("344:352").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "3" Then
        Rows("341:344").EntireRow.Hidden = False
        Rows("345:352").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "4" Then
        Rows("341:345").EntireRow.Hidden = False
        Rows("346:352").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "5" Then
        Rows("342:346").EntireRow.Hidden = False
        Rows("347:352").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "6" Then
        Rows("341:347").EntireRow.Hidden = False
        Rows("348:352").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "7" Then
        Rows("341:348").EntireRow.Hidden = False
        Rows("349:352").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "8" Then
        Rows("342:349").EntireRow.Hidden = False
        Rows("350:352").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "9" Then
        Rows("341:350").EntireRow.Hidden = False
        Rows("351").EntireRow.Hidden = True
    ElseIf Range("C340").Value = "10" Then
        Rows("341:352").EntireRow.Hidden = False
        
    End If
    'Natural England Licence Monitoring'
    If Range("F358").Value = "Select:" Then
        Rows("363:369").EntireRow.Hidden = True
    ElseIf Range("F358").Value = "1" Then
        Rows("363:365").EntireRow.Hidden = False
        Rows("366:369").EntireRow.Hidden = True
    ElseIf Range("F358").Value = "2" Then
        Rows("363:366").EntireRow.Hidden = False
        Rows("367:369").EntireRow.Hidden = True
    ElseIf Range("F358").Value = "3" Then
        Rows("363:366").EntireRow.Hidden = False
        Rows("368:369").EntireRow.Hidden = True
    ElseIf Range("F358").Value = "4" Then
        Rows("363:368").EntireRow.Hidden = False
        Rows("369").EntireRow.Hidden = True
    ElseIf Range("F358").Value = "5" Then
        Rows("363:369").EntireRow.Hidden = False
        
    End If
    'LEMP Monitoring'
    If Range("F359").Value = "Select:" Then
        Rows("370:374").EntireRow.Hidden = True
    ElseIf Range("F359").Value = "1" Then
        Rows("370").EntireRow.Hidden = False
        Rows("371:374").EntireRow.Hidden = True
    ElseIf Range("F359").Value = "2" Then
        Rows("370:371").EntireRow.Hidden = False
        Rows("372:374").EntireRow.Hidden = True
    ElseIf Range("F359").Value = "3" Then
        Rows("370:372").EntireRow.Hidden = False
        Rows("373:374").EntireRow.Hidden = True
    ElseIf Range("F359").Value = "4" Then
        Rows("370:373").EntireRow.Hidden = False
        Rows("374").EntireRow.Hidden = True
    ElseIf Range("F359").Value = "5" Then
        Rows("370:374").EntireRow.Hidden = False
        
    End If
    'LEMP Information'
    If Range("F360").Value = "Select:" Then
        Rows("375:379").EntireRow.Hidden = True
    ElseIf Range("F360").Value = "1" Then
        Rows("375").EntireRow.Hidden = False
        Rows("376:379").EntireRow.Hidden = True
    ElseIf Range("F360").Value = "2" Then
        Rows("375:376").EntireRow.Hidden = False
        Rows("377:379").EntireRow.Hidden = True
    ElseIf Range("F360").Value = "3" Then
        Rows("375:377").EntireRow.Hidden = False
        Rows("378:379").EntireRow.Hidden = True
    ElseIf Range("F360").Value = "4" Then
        Rows("375:378").EntireRow.Hidden = False
        Rows("379").EntireRow.Hidden = True
    ElseIf Range("F360").Value = "5" Then
        Rows("375:379").EntireRow.Hidden = False
        
    End If
    
    If Range("F361").Value = "Select:" Then
        Rows("380:384").EntireRow.Hidden = True
    ElseIf Range("F361").Value = "1" Then
        Rows("380").EntireRow.Hidden = False
        Rows("381:384").EntireRow.Hidden = True
    ElseIf Range("F361").Value = "2" Then
        Rows("380:381").EntireRow.Hidden = False
        Rows("382:384").EntireRow.Hidden = True
    ElseIf Range("F361").Value = "3" Then
        Rows("380:382").EntireRow.Hidden = False
        Rows("383:384").EntireRow.Hidden = True
    ElseIf Range("F361").Value = "4" Then
        Rows("380:383").EntireRow.Hidden = False
        Rows("384").EntireRow.Hidden = True
    ElseIf Range("F361").Value = "5" Then
        Rows("380:384").EntireRow.Hidden = False
        
    End If
    
    If Range("F362").Value = "Select:" Then
        Rows("385:389").EntireRow.Hidden = True
    ElseIf Range("F362").Value = "1" Then
        Rows("385").EntireRow.Hidden = False
        Rows("386:389").EntireRow.Hidden = True
    ElseIf Range("F362").Value = "2" Then
        Rows("3855:386").EntireRow.Hidden = False
        Rows("387:389").EntireRow.Hidden = True
    ElseIf Range("F362").Value = "3" Then
        Rows("385:387").EntireRow.Hidden = False
        Rows("388:389").EntireRow.Hidden = True
    ElseIf Range("F362").Value = "4" Then
        Rows("385:388").EntireRow.Hidden = False
        Rows("389").EntireRow.Hidden = True
    ElseIf Range("F362").Value = "5" Then
        Rows("385:389").EntireRow.Hidden = False
        
    End If
  
    If Range("H393").Value = "Select:" Then
        Rows("394:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "1" Then
        Rows("394:396").EntireRow.Hidden = False
        Rows("397:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "2" Then
        Rows("394:397").EntireRow.Hidden = False
        Rows("398:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "3" Then
        Rows("394:398").EntireRow.Hidden = False
        Rows("399:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "4" Then
        Rows("394:399").EntireRow.Hidden = False
        Rows("400:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "5" Then
        Rows("394:400").EntireRow.Hidden = False
        Rows("401:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "6" Then
        Rows("394:401").EntireRow.Hidden = False
        Rows("402:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "7" Then
        Rows("394:402").EntireRow.Hidden = False
        Rows("403:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "8" Then
        Rows("394:403").EntireRow.Hidden = False
        Rows("404:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "9" Then
        Rows("394:404").EntireRow.Hidden = False
        Rows("405:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "10" Then
        Rows("394:405").EntireRow.Hidden = False
        Rows("406:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "11" Then
        Rows("394:406").EntireRow.Hidden = False
        Rows("407:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "12" Then
        Rows("394:407").EntireRow.Hidden = False
        Rows("408:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "13" Then
        Rows("394:408").EntireRow.Hidden = False
        Rows("409:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "14" Then
        Rows("394:409").EntireRow.Hidden = False
        Rows("410:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "15" Then
        Rows("394:410").EntireRow.Hidden = False
        Rows("411:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "16" Then
        Rows("394:411").EntireRow.Hidden = False
        Rows("412:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "17" Then
        Rows("394:412").EntireRow.Hidden = False
        Rows("413:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "18" Then
        Rows("394:413").EntireRow.Hidden = False
        Rows("414:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "19" Then
        Rows("394:414").EntireRow.Hidden = False
        Rows("415:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "20" Then
        Rows("394:415").EntireRow.Hidden = False
        Rows("416:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "21" Then
        Rows("394:416").EntireRow.Hidden = False
        Rows("417:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "22" Then
        Rows("394:417").EntireRow.Hidden = False
        Rows("418:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "23" Then
        Rows("394:418").EntireRow.Hidden = False
        Rows("419:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "24" Then
        Rows("394:419").EntireRow.Hidden = False
        Rows("420:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "25" Then
        Rows("394:420").EntireRow.Hidden = False
        Rows("421:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "26" Then
        Rows("394:421").EntireRow.Hidden = False
        Rows("422:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "27" Then
        Rows("394:422").EntireRow.Hidden = False
        Rows("423:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "28" Then
        Rows("394:423").EntireRow.Hidden = False
        Rows("424:426").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "29" Then
        Rows("394:424").EntireRow.Hidden = False
        Rows("425").EntireRow.Hidden = True
    ElseIf Range("H393").Value = "30" Then
        Rows("394:426").EntireRow.Hidden = False
    
    End If
    
        If Range("D391").Value = "Select:" Then
        Rows("392:393").EntireRow.Hidden = True
    ElseIf Range("D391").Value = "No" Then
        Rows("392:393").EntireRow.Hidden = True
    ElseIf Range("D391").Value = "Yes" Then
        Rows("392:393").EntireRow.Hidden = False
            
    End If
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi,
Welcome to forum.
Sorry but don’t have time to work my way through all your code but there are ways you can reduce many of those If statements

As an example:

This:
Code: 
 Worksheets("Corporate Summary").Range("A16:A19,A36:A48,A215:A244").EntireRow.Hidden = CBool(Range("C22").Value = "Select:" Or Range("C22").Value = "No")

Should do same thing as all this:

Code: 
     'Cumulative Impacts in Corporate Summary For Phased Developments'    
If Range("C22").Value = "Select:" Then
        Worksheets("Corporate Summary").Range("A16:A19").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A36:A48").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A215:A244").EntireRow.Hidden = True
    ElseIf Range("C22").Value = "Yes" Then
        Worksheets("Corporate Summary").Range("A16:A19").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A36:A48").EntireRow.Hidden = False
        Worksheets("Corporate Summary").Range("A215:A244").EntireRow.Hidden = False
    ElseIf Range("C22").Value = "No" Then
        Worksheets("Corporate Summary").Range("A16:A19").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A36:A48").EntireRow.Hidden = True
        Worksheets("Corporate Summary").Range("A215:A244").EntireRow.Hidden = True
     
    End If

Perhaps you can see if can adapt the approach further in your project.

Others here may be able to offer further suggestions to help you.

Dave
 
Last edited:
Upvote 0
Also, I am not a huge fan of Else statements. I find Case statements easier to work with (see: https://www.techonthenet.com/excel/formulas/case.php)

So this:
Code: 
    If Range("C24").Value = "Select:" Then
        Rows("25").EntireRow.Hidden = True
    ElseIf Range("C24").Value = "Outline" Then
        Rows("25").EntireRow.Hidden = True
    ElseIf Range("C24").Value = "Full" Then
        Rows("25").EntireRow.Hidden = True
    ElseIf Range("C24").Value = "Hybrid" Then
        Rows("25").EntireRow.Hidden = True
    ElseIf Range("C24").Value = "Reserved Matters" Then
        Rows("25").EntireRow.Hidden = True
    ElseIf Range("C24").Value = "Other" Then
        Rows("25").EntireRow.Hidden = False
     
    End If
could be re-written like this:
Code: 
    Select Case Range("C24").Value
        Case "Select:", "Outline", "Full", "Hybrid", "Reserved Matters"
            Rows("25").EntireRow.Hidden = True
        Case "Other"
            Rows("25").EntireRow.Hidden = False
    End Select
Using this methodology in a lot of your IF THEN ELSE blocks can help reduce the size of your code considerably.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA help.
2
Replies
10
Views
432
Peter_SSs
Peter_SSs
A
  • Solved
Deactivate lines - Can the code be shortened
Replies
6
Views
298
afal1221
A
S
  • Question
VBA Help Please
2
Replies
16
Views
999
Peter_SSs
Peter_SSs
C
  • Question
Please help with VBA code with hide & unhide different rows
2
Replies
13
Views
1K
Akuini
Akuini
O
  • Solved
Combining two VBA Codes Change (ByVal Target) and (ByVal Destination)
2
Replies
10
Views
1K
OscarMDMBA
O

Forum statistics

Threads
1,216,116
Messages
6,128,930
Members
449,479
Latest member
nana abanyin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top