Hide and Un-hide rows based off a cell value (VBA)

Question

I can't seem to get this code to cooperate as intended. I Think it's a simple logic error.

If Worksheets("input").Range("B31").Value = "?TEXT" Then Worksheets("Customer Report").Rows("22").EntireRow.Hidden = True Worksheets("Customer Report").Rows("23").EntireRow.Hidden = False Else Worksheets("Customer Report").Rows("23").EntireRow.Hidden = True Worksheets("Customer Report").Rows("22").EntireRow.Hidden = False End If

The goal is that if cell B31 in Sheet 1 contains "Blah blah TEXT" then to hide row 22 and show row 23. If it just contains "Blah blah" then to show row 22 and hide row 23.

Will "TEXT" always be at the end?

Moacir
– Moacir

2017-09-22 16:43:27 +00:00
Commented Sep 22, 2017 at 16:43 — Moacir
– Moacir, Commented Sep 22, 2017 at 16:43
Yes, I was uncertain if wildcard was indicated by * or ?

Kevin P.
– Kevin P.

2017-09-22 16:45:05 +00:00
Commented Sep 22, 2017 at 16:45 — Kevin P.
– Kevin P., Commented Sep 22, 2017 at 16:45

Scott Craner · Accepted Answer · 2017-09-22 16:48:39Z

Use Like with * as the wildcard.

Worksheets("input").Range("B31").Value Like "*TEXT"

Also there is no need for the If. We can simply do the test.

Rows().Hidden = 1 = 1

The 1=1 will resolve to true and the row will be hidden.

Also Rows().EntireRow is redundant.

Worksheets("Customer Report").Rows("22").Hidden = Worksheets("input").Range("B31").Value Like "*TEXT" Worksheets("Customer Report").Rows("23").Hidden = Not Worksheets("input").Range("B31").Value Like "*TEXT"

We can lessen the amount of duplicate typing further:

Dim rng As Range Set rng = Worksheets("input").Range("B31") With Worksheets("Customer Report") .Rows("22").Hidden = rng.Value Like "*TEXT" .Rows("23").Hidden = Not rng.Value Like "*TEXT" End With

Collectives™ on Stack Overflow

Hide and Un-hide rows based off a cell value (VBA)

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related