software

Is it possible for a row to automatically be set to HIDE is one of the
cells in said row contains an asterisk?--
djarcadian
------------------------------------------------------------------------
djarcadian's Profile: www.excelforum.com/member.php...oamp;userid=15877
View this thread: www.excelforum.com/showthread...hreadid=513775You can use the autofilter and then select quot;customquot;. Select quot;does not equalquot;
and type ~* in the text box.

quot;djarcadianquot; wrote:

gt;
gt; Is it possible for a row to automatically be set to HIDE is one of the
gt; cells in said row contains an asterisk?
gt;
gt;
gt; --
gt; djarcadian
gt; ------------------------------------------------------------------------
gt; djarcadian's Profile: www.excelforum.com/member.php...oamp;userid=15877
gt; View this thread: www.excelforum.com/showthread...hreadid=513775
gt;
gt;


Thanks. That did the trick but is there a way for it to refresh
automatically? Sometimes I change data on one sheet but the filter
doesn't change to reflect these changes.--
djarcadian
------------------------------------------------------------------------
djarcadian's Profile: www.excelforum.com/member.php...oamp;userid=15877
View this thread: www.excelforum.com/showthread...hreadid=513775you can use a macro like this. Right click on the sheet tab and select quot;view
codequot;. Insert this code in quot;ThisWorkbookquot;. Warning: This macro will run
everytime you change the worksheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Range(quot;A1quot;).AutoFilter Field:=1, Criteria1:=quot;lt;gt;~*quot;, Operator:=xlAnd
End Sub

quot;djarcadianquot; wrote:

gt;
gt; Thanks. That did the trick but is there a way for it to refresh
gt; automatically? Sometimes I change data on one sheet but the filter
gt; doesn't change to reflect these changes.
gt;
gt;
gt; --
gt; djarcadian
gt; ------------------------------------------------------------------------
gt; djarcadian's Profile: www.excelforum.com/member.php...oamp;userid=15877
gt; View this thread: www.excelforum.com/showthread...hreadid=513775
gt;
gt;


It doesn't seem to work for me. Is the quot;rangequot; supposed to be just A1 or
do I put something like A1:G100?--
djarcadian
------------------------------------------------------------------------
djarcadian's Profile: www.excelforum.com/member.php...oamp;userid=15877
View this thread: www.excelforum.com/showthread...hreadid=513775djarcadian

IF what you want is to hide the row as soon as an asterisk is entered,
then you need to use the Worksheet_Change macro.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = quot;*quot; Then
Target.EntireRow.Hidden = True
End If
End Sub

Does this work for you?

Kostis VezeridesSorry for the late reply. A1 is the header of the column you are looking for
the asterisks in. If you are searching for asterisks in column C, then
change A1 to C1. Also, you might want to doublecheck that you are putting
the code under quot;ThisWorkbookquot; and not under any of the sheets or modules.

quot;djarcadianquot; wrote:

gt;
gt; It doesn't seem to work for me. Is the quot;rangequot; supposed to be just A1 or
gt; do I put something like A1:G100?
gt;
gt;
gt; --
gt; djarcadian
gt; ------------------------------------------------------------------------
gt; djarcadian's Profile: www.excelforum.com/member.php...oamp;userid=15877
gt; View this thread: www.excelforum.com/showthread...hreadid=513775
gt;
gt;

Hi, vezerid. I'm trying to do something similar to djarcadian. How exactly
does one use the Worksheet_Change macro?

My goal is to hide rows if they contain a blank in Column B and to unhide
them as soon as Column B becomes nonblank. The cells in Column B are formulas
which take their value from a different worksheet.

Suggestions?

quot;vezeridquot; wrote:

gt; djarcadian
gt;
gt; IF what you want is to hide the row as soon as an asterisk is entered,
gt; then you need to use the Worksheet_Change macro.
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; If Target.Value = quot;*quot; Then
gt; Target.EntireRow.Hidden = True
gt; End If
gt; End Sub
gt;
gt; Does this work for you?
gt;
gt; Kostis Vezerides
gt;
gt;


Hi Hubitron

What you are requesting is trickier, since the values change from
formulas. The following event macro traps the Calculate event and
performs what you ask with Sheet3 (change as necessary in the code).

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim col As Range
r = Sheets(quot;Sheet3quot;).UsedRange.Rows.Count
Application.EnableEvents = False
For i = 1 To r
If Sheets(quot;Sheet3quot;).Range(quot;Bquot; amp; i).Value = quot;quot; Then
Sheets(quot;Sheet3quot;).Range(quot;Bquot; amp; i).EntireRow.Hidden = True
Else
Sheets(quot;Sheet3quot;).Range(quot;Bquot; amp; i).EntireRow.Hidden = False
End If
Next i
Application.EnableEvents = True
End Sub

There are times that you might curse such automation. If this is the
case you will have to delete this macro.

To install (and delete later):
Alt F11 to go to the VBA editor
Ctrl R to toggle display of the Project Manager
Select your workbook and double click the ThisWorkbook icon
Paste the code above.

HTH
Kostis Vezerides
Hubitron2000 Wrote:
gt; Hi, vezerid. I'm trying to do something similar to djarcadian. How
gt; exactly
gt; does one use the Worksheet_Change macro?
gt;
gt; My goal is to hide rows if they contain a blank in Column B and to
gt; unhide
gt; them as soon as Column B becomes nonblank. The cells in Column B are
gt; formulas
gt; which take their value from a different worksheet.
gt;
gt; Suggestions?
gt;
gt; quot;vezeridquot; wrote:
gt;
gt; gt; djarcadian
gt; gt;
gt; gt; IF what you want is to hide the row as soon as an asterisk is
gt; entered,
gt; gt; then you need to use the Worksheet_Change macro.
gt; gt;
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; If Target.Value = quot;*quot; Then
gt; gt; Target.EntireRow.Hidden = True
gt; gt; End If
gt; gt; End Sub
gt; gt;
gt; gt; Does this work for you?
gt; gt;
gt; gt; Kostis Vezerides
gt; gt;
gt; gt;--
vezerid
------------------------------------------------------------------------
vezerid's Profile: www.excelforum.com/member.php...oamp;userid=28481
View this thread: www.excelforum.com/showthread...hreadid=513775Thanks a lot for the quick reply! It's getting there . . . the problem is:
every time I change a source cell, from which the column B cells take their
value, the macro runs all the way through and takes a long time. Can I speed
up the macro somehow (e.g., by restricting it to rows 5-204 and/or making it
run automatically only on the particular row whose corresponding source cell
has changed)?

I hope that makes sense . . .

Thanks again!

quot;vezeridquot; wrote:

gt;
gt; Hi Hubitron
gt;
gt; What you are requesting is trickier, since the values change from
gt; formulas. The following event macro traps the Calculate event and
gt; performs what you ask with Sheet3 (change as necessary in the code).
gt;
gt; Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
gt; Dim col As Range
gt; r = Sheets(quot;Sheet3quot;).UsedRange.Rows.Count
gt; Application.EnableEvents = False
gt; For i = 1 To r
gt; If Sheets(quot;Sheet3quot;).Range(quot;Bquot; amp; i).Value = quot;quot; Then
gt; Sheets(quot;Sheet3quot;).Range(quot;Bquot; amp; i).EntireRow.Hidden = True
gt; Else
gt; Sheets(quot;Sheet3quot;).Range(quot;Bquot; amp; i).EntireRow.Hidden = False
gt; End If
gt; Next i
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; There are times that you might curse such automation. If this is the
gt; case you will have to delete this macro.
gt;
gt; To install (and delete later):
gt; Alt F11 to go to the VBA editor
gt; Ctrl R to toggle display of the Project Manager
gt; Select your workbook and double click the ThisWorkbook icon
gt; Paste the code above.
gt;
gt; HTH
gt; Kostis Vezerides
gt; Hubitron2000 Wrote:
gt; gt; Hi, vezerid. I'm trying to do something similar to djarcadian. How
gt; gt; exactly
gt; gt; does one use the Worksheet_Change macro?
gt; gt;
gt; gt; My goal is to hide rows if they contain a blank in Column B and to
gt; gt; unhide
gt; gt; them as soon as Column B becomes nonblank. The cells in Column B are
gt; gt; formulas
gt; gt; which take their value from a different worksheet.
gt; gt;
gt; gt; Suggestions?
gt; gt;
gt; gt; quot;vezeridquot; wrote:
gt; gt;
gt; gt; gt; djarcadian
gt; gt; gt;
gt; gt; gt; IF what you want is to hide the row as soon as an asterisk is
gt; gt; entered,
gt; gt; gt; then you need to use the Worksheet_Change macro.
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; If Target.Value = quot;*quot; Then
gt; gt; gt; Target.EntireRow.Hidden = True
gt; gt; gt; End If
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; Does this work for you?
gt; gt; gt;
gt; gt; gt; Kostis Vezerides
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt; --
gt; vezerid
gt; ------------------------------------------------------------------------
gt; vezerid's Profile: www.excelforum.com/member.php...oamp;userid=28481
gt; View this thread: www.excelforum.com/showthread...hreadid=513775
gt;
gt;