CODEWITHSUNDEEP
excelvba
roybtv65
roybtv65

Reputation: 1

Excel Macro Query: Hiding Rows based on cell value

Hi I've tried the below code but it doesn't seem to be working can anybody help me. I just want to hide rows 3 & 4 when B1 has the text Delete. But I want this to run automatically when Delete is entered into B1.

Thanks

Code

Upvotes: 0

Views: 511

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Something like this should work

Option Explicit 'Very first line to ensure that variables are declared

Private Sub Worksheet_Change(ByVal Target As Range)
    'check if target address is B1
    If Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) = "B1" Then
        ShowHideRows
    End If
End Sub

Public Sub ShowHideRows()
    If Range("B1").Value = "Delete" Then
        Rows("3:4").EntireRow.Hidden = True
        Rows("7:8").EntireRow.Hidden = False
    ElseIf Range("B1").Value = "Open" Then 'use ElseIf if possible like here
        Rows("3:4").EntireRow.Hidden = False
        Rows("7:8").EntireRow.Hidden = True
    End If
End Sub

Note: This is case sensitive. So if you type in B1 delete instead of Delete it will not run. To make it non case sensitive use LCase() like: 

LCase(Range("B1").Value) = "delete" 'string delete must be lower case!

Upvotes: 1

Related Questions