Excel 'If Range' hiding rows automatically on a spreadsheet

Anonymous
2024-02-02T13:48:05+00:00

New to coding so this is probably really simple to most but I have a spreadsheet that needs to automatically hide various rows when a particular cell contains certain wording i.e. "Buildings other than dwellings", cell "C4" could also contain different wording i.e. "Dwellings including blocks of flats" whereby the same spreadsheet needs to hide a different set of rows.

Below works fine for just "Buildings other than dwellings" and shows all rows if the cell "C4" is blank but how do it get it to work whilst incorporating the option for "Dwellings including blocks of flats" also?

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C4") = "Buildings other than dwellings" Then

Rows("10:18").EntireRow.Hidden = True

Rows("38:43").EntireRow.Hidden = True

Rows("84:87").EntireRow.Hidden = True

Rows("92:95").EntireRow.Hidden = True

Rows("103:109").EntireRow.Hidden = True

Else

Rows("6:119").EntireRow.Hidden = False

End If

End Sub

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2024-02-02T16:39:00+00:00

To incorporate the option for "Dwellings including blocks of flats" in your code, you can use an "ElseIf" statement. Here's an example code that should work for your scenario:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C4") = "Buildings other than dwellings" Then

    Rows("10:18").EntireRow.Hidden = True

    Rows("38:43").EntireRow.Hidden = True

    Rows("84:87").EntireRow.Hidden = True

    Rows("92:95").EntireRow.Hidden = True

    Rows("103:109").EntireRow.Hidden = True

ElseIf Range("C4") = "Dwellings including blocks of flats" Then

    Rows("20:28").EntireRow.Hidden = True

    Rows("48:53").EntireRow.Hidden = True

    Rows("99:102").EntireRow.Hidden = True

    Rows("114:119").EntireRow.Hidden = True

Else

    Rows("6:119").EntireRow.Hidden = False

End If

End Sub

In this code, if cell C4 contains "Dwellings including blocks of flats", it will hide rows 20 to 28, 48 to 53, 99 to 102, and 114 to 119. If cell C4 contains any other value or is blank, it will show all rows from 6 to 119.

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2024-02-02T17:13:30+00:00

    Thank you very much that works fine,

    It leads me onto another query however which is given that the document in question has to be protected to prevent user input into specific cells how do I get the above code to hide the rows required i.e. Currently get a Run-time error '1004': Unable to set the Hidden property of the Range class

    Is it a case of incorporating an 'unprotect sheet' code at the beginning and then 'protect sheet' at the end or is there a simpler way?

    Was this answer helpful?

    0 comments