VBA to hide cells in excel I am tyring to create macro that will hide cells depe
ID: 3560564 • Letter: V
Question
VBA to hide cells in excel
I am tyring to create macro that will hide cells depending on what data is in the cell. I am currently using this and it is working but I want to be able to use 2 or more as I have different cells that I want hidden depending on what is entered into other cells.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("F5") = "Don't Show" Then
Rows("8:9").EntireRow.Hidden = True
Else
Rows("8:9").EntireRow.Hidden = False
End If
End Sub
But I also want this as well. How can I add them together?
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("F9") = "Don't Show" Then
Rows("8:9").EntireRow.Hidden = True
Else
Rows("8:9").EntireRow.Hidden = False
End If
End Sub
So what I want is that if cell F5 = "Don't Show" I want rows 8 and 9 hidden. If it = "Show" then I want rows 8 and 9 to appear. So they will be hidden most of the time.
I also want it that if cell F9 = "Don't Show" I want rows 8 and 9 to be hidden. If it = Show then I want rows 8 and 9 to appear.
Does this make sense?
Explanation / Answer
%^& And #@what if F5 says Show and F9 says Don't Show? There would appear to be an inherent flaw in the logic that isn't covered. I'll assume that Don't Show takes precedence over Show.@@
Right-click the worksheet's name tab and choose View Code. When the VBE opens, paste the following into the pane titled something like Book1 - Sheet1 (Code), 1b
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F5, F9")) Is Nothing Then
On Error GoTo FallThrough
Application.EnableEvents = False
Me.Rows("8:9").Hidden = _
(LCase(Range("F5")) = "don't show" Or LCase(Range("F9")) = "don't show")
End If
FallThrough:
Application.EnableEvents = True
End Sub
Tap Alt+Q to return to your worksheet. If F5 or F9 contains any non-case sensitive version of Don't Show then rows 8 & 9 will be hidden.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.