PDA

View Full Version : Excel VBA to Unhide rows with a specific value in a Range



simora
03-21-2014, 06:03 AM
I have an Excel range that is hidden. I want to unhide only rows in that range C2:F21 that contain a specific value. The value comes from a userform in TextBox3.

Since find does NOT work on hidden ranges, as far as I can tell, how can I use the Match function to look into the range to do this, OR is there some other useable approach using VBA
( Preferred Solution )

Thanks

p45cal
03-21-2014, 07:31 AM
You want to find the value that's in TextBox three among ALL the cells in C2:F21 (ie. all columns and all rows of that range)?

ps.
.Find will search hidden cells if you use LookIn:=xlFormulas, assuming there are no formulas, that is.

simora
03-21-2014, 03:17 PM
You want to find the value that's in TextBox three among ALL the cells in C2:F21 (ie. all columns and all rows of that range)?

ps.
.Find will search hidden cells if you use LookIn:=xlFormulas, assuming there are no formulas, that is.

simora
03-21-2014, 03:19 PM
p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal):

RE: You want to find the value that's in TextBox three among ALL the cells in C2:F21 (ie. all columns and all rows of that range)?

To answer your question: Yes!

p45cal
03-21-2014, 04:38 PM
Something along these lines
Private Sub CommandButton1_Click()
For Each rw In Range("C2:F21").Rows
For Each cll In rw.Cells
If cll.Text = TextBox3.Value Then
cll.EntireRow.Hidden = False
Exit For
End If
Next cll
Next rw
End Sub

simora
03-22-2014, 04:41 PM
p45cal:

Thanks.

The code worked as expected without any errors.