CODEWITHSUNDEEP
excelvba
PGD15
PGD15

Reputation: 183

VBA hide cells based on value

I've written the following where I want the row 6:10 to be hidden if the value in cell e3 is not DWW, if it is DWW then I went the cells to reappear. 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E3")) Is Nothing Then
        If LCase$(Range("E3").Value) = "DWW" Then
            'if "DWW" is entered in E3, rows are Visible
            Rows("6:10").EntireRow.Hidden = False
        Else
            'if E3=blank or anything other than "DWW", hide rows
            Rows("6:10").EntireRow.Hidden = True
        End If
    End If
End Sub

My formula doesn't get the cells to reappear could anyone please help with this?

Thanks in advance.

Upvotes: 1

Views: 560

Answers (2)

QHarr
QHarr

Reputation: 84465

You can been given a far more efficient answer already but you could use regex

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E3")) Is Nothing Then
        Rows("6:10").EntireRow.Hidden = True
        With CreateObject("VBScript.RegExp")
            .Global = True
            .MultiLine = True
            .Pattern = "DWW"
            If .test(Target) And Len(Target) = 3 Then Rows("6:10").EntireRow.Hidden = False
        End With
    End If
End Sub

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

LCase will change the value to lower case.

Either use Ucase:

If UCase$(Range("E3").Value) = "DWW" Then

Or match to the lower case:

If LCase$(Range("E3").Value) = "dww" Then

Upvotes: 3

Related Questions