Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically hide row if cell contains asterisk
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: http://www.excelforum.com/member.php...o&userid=15877 View this thread: http://www.excelforum.com/showthread...hreadid=513775 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically hide row if cell contains asterisk
You can use the autofilter and then select "custom". Select "does not equal"
and type ~* in the text box. "djarcadian" wrote: 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: http://www.excelforum.com/member.php...o&userid=15877 View this thread: http://www.excelforum.com/showthread...hreadid=513775 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically hide row if cell contains asterisk
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: http://www.excelforum.com/member.php...o&userid=15877 View this thread: http://www.excelforum.com/showthread...hreadid=513775 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically hide row if cell contains asterisk
you can use a macro like this. Right click on the sheet tab and select "view
code". Insert this code in "ThisWorkbook". Warning: This macro will run everytime you change the worksheet. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Range("A1").AutoFilter Field:=1, Criteria1:="<~*", Operator:=xlAnd End Sub "djarcadian" wrote: 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: http://www.excelforum.com/member.php...o&userid=15877 View this thread: http://www.excelforum.com/showthread...hreadid=513775 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically hide row if cell contains asterisk
It doesn't seem to work for me. Is the "range" supposed to be just A1 or do I put something like A1:G100? -- djarcadian ------------------------------------------------------------------------ djarcadian's Profile: http://www.excelforum.com/member.php...o&userid=15877 View this thread: http://www.excelforum.com/showthread...hreadid=513775 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically hide row if cell contains asterisk
djarcadian
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 = "*" Then Target.EntireRow.Hidden = True End If End Sub Does this work for you? Kostis Vezerides |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically hide row if cell contains asterisk
Sorry 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 "ThisWorkbook" and not under any of the sheets or modules. "djarcadian" wrote: It doesn't seem to work for me. Is the "range" supposed to be just A1 or do I put something like A1:G100? -- djarcadian ------------------------------------------------------------------------ djarcadian's Profile: http://www.excelforum.com/member.php...o&userid=15877 View this thread: http://www.excelforum.com/showthread...hreadid=513775 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically hide row if cell contains asterisk
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? "vezerid" wrote: djarcadian 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 = "*" Then Target.EntireRow.Hidden = True End If End Sub Does this work for you? Kostis Vezerides |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically hide row if cell contains asterisk
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("Sheet3").UsedRange.Rows.Count Application.EnableEvents = False For i = 1 To r If Sheets("Sheet3").Range("B" & i).Value = "" Then Sheets("Sheet3").Range("B" & i).EntireRow.Hidden = True Else Sheets("Sheet3").Range("B" & 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: 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? "vezerid" wrote: djarcadian 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 = "*" Then Target.EntireRow.Hidden = True End If End Sub Does this work for you? Kostis Vezerides -- vezerid ------------------------------------------------------------------------ vezerid's Profile: http://www.excelforum.com/member.php...o&userid=28481 View this thread: http://www.excelforum.com/showthread...hreadid=513775 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically hide row if cell contains asterisk
Thanks 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! "vezerid" wrote: 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("Sheet3").UsedRange.Rows.Count Application.EnableEvents = False For i = 1 To r If Sheets("Sheet3").Range("B" & i).Value = "" Then Sheets("Sheet3").Range("B" & i).EntireRow.Hidden = True Else Sheets("Sheet3").Range("B" & 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: 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? "vezerid" wrote: djarcadian 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 = "*" Then Target.EntireRow.Hidden = True End If End Sub Does this work for you? Kostis Vezerides -- vezerid ------------------------------------------------------------------------ vezerid's Profile: http://www.excelforum.com/member.php...o&userid=28481 View this thread: http://www.excelforum.com/showthread...hreadid=513775 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy combobox - cell link to change automatically | Excel Worksheet Functions | |||
Edit cell automatically takes you to cell | Excel Worksheet Functions | |||
Add asterisk to excel cell | Excel Worksheet Functions | |||
Hide text that doesn't fit in the cell | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |