Hide/unhide certain rows if cell is populated

[sauce4u]

Hello Everyone,

Long time listener, first time caller.
I am trying to Hide/Unhide certain rows when a certain cell is populated by text, not assigned text either, just any text.

ex.
If "A1" is populated, then rows 5 $ 11-16 will Unhide. if "A1" has nothing in the cell the rows will hide.

I currently have put together the following (mostly from posts on this site)
It will hide/unhide rows when i enter the value "1" but i wan to change that to any text entered, not a value. Also, with the code i have when it hides the cells "flutter" for a couple seconds. is there any way to get rid of this? Thanks in advance!!!!!!!

Workbook
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call unhide
End If
End Sub

Module
Sub unhide()
Range("A1").Select
If ActiveCell.FormulaR1C1 = "1" Then
Rows("4:6").Select
Selection.EntireRow.Hidden = False
Rows("10:17").Select
Selection.EntireRow.Hidden = False
Else: Selection.ClearContents
Rows("5:5").Select
Selection.EntireRow.Hidden = True
Rows("11:16").Select
Selection.EntireRow.Hidden = True
End If
End Sub

 

[Smitty]

Welcome to the Board! (As a poster anyway )

This should do what you want:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A1")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> LenB(Target.Value) <> 0 <SPAN style="color:#00007F">Then</SPAN><br>                Range("A5,A11:A16").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>            Else: Range("A5,A11:A16").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

As for the flickering you can put Application.ScreenUpdating = False at the beginning of your code and set it back to true at the end.

HTH,

 

[sauce4u]

It reversed what funtion i needed but i just switched the True/False at the end. Thank you!!!!!!!!!! was working on that for a while. Very much appreciated!!