Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate hide row
Is there a formula i can set to automate hiding a row when a
particular cell returns a zero value? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate hide row
If you are gonna be sure that the cell value will be a constant (not
formula) then, use the following procedure in the given sheet module, and save the workbook. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then If Target.Value = 0 Then Target.EntireRow.Hidden = True End If End If End Sub You may try using the following module if the result 0 will be due to formula, and not constant. Private Sub Worksheet_Calculate() For i = 1 To ThisWorkbook.Sheets(Sheet1).UsedRange.Rows.Count If Cells(i, 1).Value = 0 Then Cells(i, 1).EntireRow.Hidden = True Else Cells(i, 1).EntireRow.Hidden = False End If Next i End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, & other resources Please visit http://socko.wordpress.com/ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate hide row
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H:H" '<=== change to suit If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Me.Rows(.Row).Hidden = .Value = 0 End With End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "Nicole" wrote in message ... Is there a formula i can set to automate hiding a row when a particular cell returns a zero value? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate hide row
Hi Bob,
I have paste the below string into the view code page, nothing happens, should i click something after pasting and close the view code page? Let me show a simple example: Fruit Jan Feb Mar A 1 1 1 B 2 3 1 C 0 0 0 D 1 1 1 Altogether I have col A to K and row 3 to 400, i want to hide row like C above automatically Nicole On Dec 18, 4:52*pm, "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H:H" '<=== change to suit * * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then * * * * With Target * * * * * * Me.Rows(.Row).Hidden = .Value = 0 * * * * End With * * End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "Nicole" wrote in message ... Is there a formula i can set toautomatehiding arowwhen a particular cell returns a zero value?-Hidequoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate hide row
On Dec 18, 4:51*pm, Socko wrote:
If you are gonna be sure that the cell value will be a constant (not formula) then, use the following procedure in the given sheet module, and save the workbook. * * * * Private Sub Worksheet_Change(ByVal Target As Range) * * * * If Target.Column = 1 Then * * * * * If Target.Value = 0 Then * * * * * * Target.EntireRow.Hidden = True * * * * * End If * * * * End If * * * * End Sub You may try using the following module if the result 0 will be due to formula, and not constant. * * * * Private Sub Worksheet_Calculate() * * * * For i = 1 To ThisWorkbook.Sheets(Sheet1).UsedRange.Rows..Count * * * * * If Cells(i, 1).Value = 0 Then * * * * * * Cells(i, 1).EntireRow.Hidden = True * * * * * Else * * * * * * Cells(i, 1).EntireRow.Hidden = False * * * * * End If * * * * Next i * * * * End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, & other resources Please visithttp://socko.wordpress.com/ Hi Socko, I have copied and paste the Calculate section into the view code and save, nothing seem to happen, those rows with the blank cell on column P still there. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate hide row
My code is designed to run when you enter the value. So you would change H:H
to B:K and then set a value. -- __________________________________ HTH Bob "Nicole" wrote in message ... Hi Bob, I have paste the below string into the view code page, nothing happens, should i click something after pasting and close the view code page? Let me show a simple example: Fruit Jan Feb Mar A 1 1 1 B 2 3 1 C 0 0 0 D 1 1 1 Altogether I have col A to K and row 3 to 400, i want to hide row like C above automatically Nicole On Dec 18, 4:52 pm, "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H:H" '<=== change to suit If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Me.Rows(.Row).Hidden = .Value = 0 End With End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "Nicole" wrote in message ... Is there a formula i can set toautomatehiding arowwhen a particular cell returns a zero value?-Hidequoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate hide row
On Dec 19, 4:30*am, "Bob Phillips" wrote:
My code is designed to run when you enter the value. So you would change H:H to B:K and then set a value. -- __________________________________ HTH Bob "Nicole" wrote in message ... Hi Bob, I have paste the below string into the view code page, nothing happens, should i click something after pasting and close the view code page? Let me show a simple example: Fruit *Jan Feb Mar A * * * *1 * *1 * *1 B * * * *2 * 3 * * 1 C * * * *0 * *0 * *0 D * * * *1 * *1 * *1 Altogether I have col A to K and row 3 to 400, i want tohiderow like C above automatically Nicole On Dec 18, 4:52 pm, "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H:H" '<=== change to suit If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Me.Rows(.Row).Hidden = .Value = 0 End With End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "Nicole" wrote in message .... Is there a formula i can set toautomatehiding arowwhen a particular cell returns a zero value?-Hidequoted text - - Show quoted text --Hidequoted text - - Show quoted text - Thanks, my problem is resolved. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide / UnHide - is there a Function to automate this ? | Excel Worksheet Functions | |||
Automate display/ hide rows in excel based on yes/no check box | Excel Discussion (Misc queries) | |||
Automate without add-ins | Excel Discussion (Misc queries) | |||
Automate Add-in | Excel Discussion (Misc queries) | |||
How do I hide a worksheet in Excel and use a password to un-hide . | Excel Discussion (Misc queries) |