Auto Hide Rows Based On Cell Value

C

ChillBee

New Member
Joined
Jan 17, 2025
Messages
1
Office Version
  1. 2024
Platform
  1. Windows
Hi, very new to VBA, trying to hide rows based on cell value changes and have it update every time a drop down menu is used. Below is what I've done but it doesn't work..
The cells B4 and so on have a formula which its result changes based on said drop down. Not sure if there's more information I could provide.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet

Dim hideValue As String



Set ws = ThisWorkbook.Worksheets("Interval Breakdown")



If Target.Address = "$A$2" Then ' Check if A2 changed



' Range CDA

hideValue = ws.Range("B4").Value

If hideValue = "0" Then

ws.Range("A20:A23").Hidden = True

ws.Range("A56:A59").Hidden = True

ws.Range("A92:A95").Hidden = True

ws.Range("A128:A131").Hidden = True

ws.Range("A164:A167").Hidden = True

ws.Range("A200:A203").Hidden = True

ws.Range("A236:A239").Hidden = True

ws.Range("A281:A283").Hidden = True

ws.Range("A305:A308").Hidden = True

ws.Range("A350:A352").Hidden = True

ElseIf hideValue > "0" Then

ws.Range("A20:A23").Hidden = False

ws.Range("A56:A59").Hidden = False

ws.Range("A92:A95").Hidden = False

ws.Range("A128:A131").Hidden = False

ws.Range("A164:A167").Hidden = False

ws.Range("A200:A203").Hidden = False

ws.Range("A236:A239").Hidden = False

ws.Range("A281:A283").Hidden = False

ws.Range("A305:A308").Hidden = False

ws.Range("A350:A352").Hidden = False

End If



' Range MYS

hideValue = ws.Range("B5").Value

If hideValue = "0" Then

ws.Range("A24:A29").Hidden = True

ws.Range("A60:A65").Hidden = True

ws.Range("A96:A101").Hidden = True

ws.Range("A132:A137").Hidden = True

ws.Range("A168:A173").Hidden = True

ws.Range("A204:A209").Hidden = True

ws.Range("A240:A245").Hidden = True

ws.Range("A284:A286").Hidden = True

ws.Range("A309:A312").Hidden = True

ws.Range("A353:A355").Hidden = True

ElseIf hideValue > "0" Then

ws.Range("A24:A29").Hidden = False

ws.Range("A60:A65").Hidden = False

ws.Range("A96:A101").Hidden = False

ws.Range("A132:A137").Hidden = False

ws.Range("A168:A173").Hidden = False

ws.Range("A204:A209").Hidden = False

ws.Range("A240:A245").Hidden = False

ws.Range("A284:A286").Hidden = False

ws.Range("A309:A312").Hidden = False

ws.Range("A353:A355").Hidden = False

End If



' Range POL

hideValue = ws.Range("B6").Value

If hideValue = "0" Then

ws.Range("A30:A33").Hidden = True

ws.Range("A66:A69").Hidden = True

ws.Range("A102:A105").Hidden = True

ws.Range("A138:A141").Hidden = True

ws.Range("A174:A177").Hidden = True

ws.Range("A210:A213").Hidden = True

ws.Range("A246:A249").Hidden = True

ws.Range("A287:A289").Hidden = True

ws.Range("A313:A316").Hidden = True

ws.Range("A356:A358").Hidden = True

ElseIf hideValue > "0" Then

ws.Range("A30:A33").Hidden = False

ws.Range("A66:A69").Hidden = False

ws.Range("A102:A105").Hidden = False

ws.Range("A138:A141").Hidden = False

ws.Range("A174:A177").Hidden = False

ws.Range("A210:A213").Hidden = False

ws.Range("A246:A249").Hidden = False

ws.Range("A287:A289").Hidden = False

ws.Range("A313:A316").Hidden = False

ws.Range("A356:A358").Hidden = False

End If



' Range POR

hideValue = ws.Range("B7").Value

If hideValue = "0" Then

ws.Range("A34:A37").Hidden = True

ws.Range("A70:A73").Hidden = True

ws.Range("A106:A109").Hidden = True

ws.Range("A142:A145").Hidden = True

ws.Range("A178:A181").Hidden = True

ws.Range("A214:A217").Hidden = True

ws.Range("A250:A253").Hidden = True

ws.Range("A290:A292").Hidden = True

ws.Range("A317:A320").Hidden = True

ws.Range("A359:A361").Hidden = True

ElseIf hideValue > "0" Then

ws.Range("A34:A37").Hidden = False

ws.Range("A70:A73").Hidden = False

ws.Range("A106:A109").Hidden = False

ws.Range("A142:A145").Hidden = False

ws.Range("A178:A181").Hidden = False

ws.Range("A214:A217").Hidden = False

ws.Range("A250:A253").Hidden = False

ws.Range("A290:A292").Hidden = False

ws.Range("A317:A320").Hidden = False

ws.Range("A359:A361").Hidden = False

End If

End If

End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
You can't hide just cells, you need to use .EntireRow.Hidden

hideValue is likely the number zero (no quotes around 0) and not a string ("0")

I would think your requirements are b4 to b7 being zero or not zero
but if there are different requirements should b4 to b7 be positive or negative let us know.

Try this
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet
    Dim hideValue As String

Set ws = ThisWorkbook.Worksheets("Interval Breakdown")

If Target.Address = "$A$2" Then ' Check if A2 changed
        ' Range CDA
        hideValue = ws.Range("B4").Value
        If hideValue = 0 Then
            ws.Range("A20:A23").EntireRow.Hidden = True
            ws.Range("A56:A59").EntireRow.Hidden = True
            ws.Range("A92:A95").EntireRow.Hidden = True
            ws.Range("A128:A131").EntireRow.Hidden = True
            ws.Range("A164:A167").EntireRow.Hidden = True
            ws.Range("A200:A203").EntireRow.Hidden = True
            ws.Range("A236:A239").EntireRow.Hidden = True
            ws.Range("A281:A283").EntireRow.Hidden = True
            ws.Range("A305:A308").EntireRow.Hidden = True
            ws.Range("A350:A352").EntireRow.Hidden = True
        Else    'If hideValue <> 0 Then
            ws.Range("A20:A23").EntireRow.Hidden = False
            ws.Range("A56:A59").EntireRow.Hidden = False
            ws.Range("A92:A95").EntireRow.Hidden = False
            ws.Range("A128:A131").EntireRow.Hidden = False
            ws.Range("A164:A167").EntireRow.Hidden = False
            ws.Range("A200:A203").EntireRow.Hidden = False
            ws.Range("A236:A239").EntireRow.Hidden = False
            ws.Range("A281:A283").EntireRow.Hidden = False
            ws.Range("A305:A308").EntireRow.Hidden = False
            ws.Range("A350:A352").EntireRow.Hidden = False
        End If

        ' Range MYS
        hideValue = ws.Range("B5").Value
        If hideValue = 0 Then
            ws.Range("A24:A29").EntireRow.Hidden = True
            ws.Range("A60:A65").EntireRow.Hidden = True
            ws.Range("A96:A101").EntireRow.Hidden = True
            ws.Range("A132:A137").EntireRow.Hidden = True
            ws.Range("A168:A173").EntireRow.Hidden = True
            ws.Range("A204:A209").EntireRow.Hidden = True
            ws.Range("A240:A245").EntireRow.Hidden = True
            ws.Range("A284:A286").EntireRow.Hidden = True
            ws.Range("A309:A312").EntireRow.Hidden = True
            ws.Range("A353:A355").EntireRow.Hidden = True
        Else    'If hideValue <> 0 Then
            ws.Range("A24:A29").EntireRow.Hidden = False
            ws.Range("A60:A65").EntireRow.Hidden = False
            ws.Range("A96:A101").EntireRow.Hidden = False
            ws.Range("A132:A137").EntireRow.Hidden = False
            ws.Range("A168:A173").EntireRow.Hidden = False
            ws.Range("A204:A209").EntireRow.Hidden = False
            ws.Range("A240:A245").EntireRow.Hidden = False
            ws.Range("A284:A286").EntireRow.Hidden = False
            ws.Range("A309:A312").EntireRow.Hidden = False
            ws.Range("A353:A355").EntireRow.Hidden = False
        End If

        ' Range POL
        hideValue = ws.Range("B6").Value
        If hideValue = 0 Then
            ws.Range("A30:A33").EntireRow.Hidden = True
            ws.Range("A66:A69").EntireRow.Hidden = True
            ws.Range("A102:A105").EntireRow.Hidden = True
            ws.Range("A138:A141").EntireRow.Hidden = True
            ws.Range("A174:A177").EntireRow.Hidden = True
            ws.Range("A210:A213").EntireRow.Hidden = True
            ws.Range("A246:A249").EntireRow.Hidden = True
            ws.Range("A287:A289").EntireRow.Hidden = True
            ws.Range("A313:A316").EntireRow.Hidden = True
            ws.Range("A356:A358").EntireRow.Hidden = True
        Else    'If hideValue <> 0 Then
            ws.Range("A30:A33").EntireRow.Hidden = False
            ws.Range("A66:A69").EntireRow.Hidden = False
            ws.Range("A102:A105").EntireRow.Hidden = False
            ws.Range("A138:A141").EntireRow.Hidden = False
            ws.Range("A174:A177").EntireRow.Hidden = False
            ws.Range("A210:A213").EntireRow.Hidden = False
            ws.Range("A246:A249").EntireRow.Hidden = False
            ws.Range("A287:A289").EntireRow.Hidden = False
            ws.Range("A313:A316").EntireRow.Hidden = False
            ws.Range("A356:A358").EntireRow.Hidden = False
        End If

        ' Range POR
        hideValue = ws.Range("B7").Value
        If hideValue = 0 Then
            ws.Range("A34:A37").EntireRow.Hidden = True
            ws.Range("A70:A73").EntireRow.Hidden = True
            ws.Range("A106:A109").EntireRow.Hidden = True
            ws.Range("A142:A145").EntireRow.Hidden = True
            ws.Range("A178:A181").EntireRow.Hidden = True
            ws.Range("A214:A217").EntireRow.Hidden = True
            ws.Range("A250:A253").EntireRow.Hidden = True
            ws.Range("A290:A292").EntireRow.Hidden = True
            ws.Range("A317:A320").EntireRow.Hidden = True
            ws.Range("A359:A361").EntireRow.Hidden = True
        Else    'If hideValue <> 0 Then
            ws.Range("A34:A37").EntireRow.Hidden = False
            ws.Range("A70:A73").EntireRow.Hidden = False
            ws.Range("A106:A109").EntireRow.Hidden = False
            ws.Range("A142:A145").EntireRow.Hidden = False
            ws.Range("A178:A181").EntireRow.Hidden = False
            ws.Range("A214:A217").EntireRow.Hidden = False
            ws.Range("A250:A253").EntireRow.Hidden = False
            ws.Range("A290:A292").EntireRow.Hidden = False
            ws.Range("A317:A320").EntireRow.Hidden = False
            ws.Range("A359:A361").EntireRow.Hidden = False
        End If

End If

End Sub
 
Upvote 0
Just an observation, you could rewrite this:

VBA Code: 
If Target.Address = "$A$2" Then ' Check if A2 changed
    ' Range CDA
    hideValue = ws.Range("B4").Value
    If hideValue = 0 Then
        ws.Range("A20:A23").EntireRow.Hidden = True
        ws.Range("A56:A59").EntireRow.Hidden = True
        ws.Range("A92:A95").EntireRow.Hidden = True
        ws.Range("A128:A131").EntireRow.Hidden = True
        ws.Range("A164:A167").EntireRow.Hidden = True
        ws.Range("A200:A203").EntireRow.Hidden = True
        ws.Range("A236:A239").EntireRow.Hidden = True
        ws.Range("A281:A283").EntireRow.Hidden = True
        ws.Range("A305:A308").EntireRow.Hidden = True
        ws.Range("A350:A352").EntireRow.Hidden = True
    Else    'If hideValue <> 0 Then
        ws.Range("A20:A23").EntireRow.Hidden = False
        ws.Range("A56:A59").EntireRow.Hidden = False
        ws.Range("A92:A95").EntireRow.Hidden = False
        ws.Range("A128:A131").EntireRow.Hidden = False
        ws.Range("A164:A167").EntireRow.Hidden = False
        ws.Range("A200:A203").EntireRow.Hidden = False
        ws.Range("A236:A239").EntireRow.Hidden = False
        ws.Range("A281:A283").EntireRow.Hidden = False
        ws.Range("A305:A308").EntireRow.Hidden = False
        ws.Range("A350:A352").EntireRow.Hidden = False
    End If

Like this:
VBA Code: 
If Target.Address = "$A$2" Then ' Check if A2 changed
    ' Range CDA
    If ws.Range("B4") = 0 Then
        ws.Range("20:23,56:59,92:95,128:131,164:167,200:203,236:239,281:283,305:308,350:352").EntireRow.Hidden = True
    Else
        ws.Range("20:23,56:59,92:95,128:131,164:167,200:203,236:239,281:283,305:308,350:352").EntireRow.Hidden = False
    End If
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA Help to Auto hide/unhide rows based on cell value
Replies
8
Views
3K
IFTODE
I
M
  • Question Question
Auto hide columns and rows based on cell values. Protect Sheet.
Replies
2
Views
2K
mcarterlgk
M
L
  • Question Question
Auto Hide rows based on cell value
2
Replies
11
Views
5K
ludlow
L
P
  • Question Question
VB Excel - Attempting to Call in VB - Not working
Replies
8
Views
667
skywriter
skywriter
Z
  • Question Question
Errors in combining code
Replies
5
Views
457
Trevor G
Trevor G

Forum statistics

Threads
1,225,730
Messages
6,186,701
Members
453,369
Latest member
positivemind

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