Excel/VBA Show or hide rows based on specific cell values

Question

I am trying to find a way to find a way to show or hide rows 55:57 based on the value in Cell J52. Ideally, I would want this to happen in Real-Time Using VBA.

The value in J52 goes from 0 - 3.

I tried this code but it doesn't work as it throws an error stating "Sub or Function not defined":

Private Sub Worksheet_SelectionChange(ByVal Target As Range) StartRow = 55 EndRow = 57 Tar = C52 For i = Tar To Tar If Cell(i).Value = "Individuals" Then Cells(StartRow, EndRow).EntireRow.Hidden = True Else Cells(StartRow, EndRow).EntireRow.Hidden = False End If Next i End Sub

Any help would be appreciated. Thanks.

You're getting that message as you're using Cell(i).Value instead of Cells(i).Value. You're code won't work though as Tar = C52 will create Tar as a variant and C52 as a variant - I guess it's a cell range, but to VBA C52 is just an undefined variable (which Option Explicit would highlight). So Tar and C52 will equal 0. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jan 4, 2023 at 9:56
Also, Worksheet_SelectionChange will fire when you move from one cell to another (you change the selected cell), rather than when you change cell C52 value. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jan 4, 2023 at 9:57
Your code also appears to look at C52 being "Individuals", but your question says based on J52 which contains the value 0 - 3? — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jan 4, 2023 at 10:01

Darren Bartrup-Cook · Accepted Answer · 2023-01-04 10:03:03Z

This code will only fire when you change the value in C52. It does what your code suggests, rather than what your question suggests.

(Target.Value = "Individuals") will return TRUE/FALSE based on the value of Target - Target being the cell you changed (C52).

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$52" Then Range("55:57").EntireRow.Hidden = (Target.Value = "Individuals") End If End Sub

Spencer Barnes · Accepted Answer · 2023-01-04 09:59:57Z

This is slightly lazy coding but I think it achieves what you're after?

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("55:57").EntireRow.Hidden = (Range("C52") = "Individuals") End Sub

Collectives™ on Stack Overflow

Excel/VBA Show or hide rows based on specific cell values

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related