Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows within range if B? = X
Greetings all:
Situation: Formula in each cell in column B: Cell B? has a "x" if no quantity entered in cell E? Cell B? has an "*" if a quantity IS entered in cell E? Range: B788 thru B846 Range Named: T_Box_R Looking for (High Speed) code to Hide all rows with "*" in column B, WITHIN the Specified Named Range ONLY. The code will be operated with a toggle button that will: 1: Activate the hide code 2: Unhide all hidden rows within the specified Named Range There will be numerous incidents of this used within one Worksheet, each with its own toggle, operating on a different Named Range. All help gratefully received!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows within range if B? = X
You will have to put the name of your toggle button in my code. This should
work for you. Option Explicit Sub HideRows() Dim cell As Range Application.ScreenUpdating = False If tgButton = True Then For Each cell In Range("T_Box_R") If cell.Value = "*" Then cell.EntireRow.Hidden = True End If Next cell Else Range("T_Box_R").Rows.EntireRow.Hidden = False End If Application.ScreenUpdating = False End Sub -- Cheers, Ryan "BEEJAY" wrote: Greetings all: Situation: Formula in each cell in column B: Cell B? has a "x" if no quantity entered in cell E? Cell B? has an "*" if a quantity IS entered in cell E? Range: B788 thru B846 Range Named: T_Box_R Looking for (High Speed) code to Hide all rows with "*" in column B, WITHIN the Specified Named Range ONLY. The code will be operated with a toggle button that will: 1: Activate the hide code 2: Unhide all hidden rows within the specified Named Range There will be numerous incidents of this used within one Worksheet, each with its own toggle, operating on a different Named Range. All help gratefully received!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows within range if B? = X
Hi,
Right click your sheet tab, view code and paste this in and run it and it will toggle between hiden and visible each time you run it. Sub HideEmFast() Dim HideRange As Range For Each c In Range("T_Box_R") If c.Value = "*" Then If HideRange Is Nothing Then Set HideRange = c.EntireRow Else Set HideRange = Union(HideRange, c.EntireRow) End If End If Next If HideRange.EntireRow.Hidden Then HideRange.EntireRow.Hidden = False Else HideRange.EntireRow.Hidden = True End If End Sub Mike "BEEJAY" wrote: Greetings all: Situation: Formula in each cell in column B: Cell B? has a "x" if no quantity entered in cell E? Cell B? has an "*" if a quantity IS entered in cell E? Range: B788 thru B846 Range Named: T_Box_R Looking for (High Speed) code to Hide all rows with "*" in column B, WITHIN the Specified Named Range ONLY. The code will be operated with a toggle button that will: 1: Activate the hide code 2: Unhide all hidden rows within the specified Named Range There will be numerous incidents of this used within one Worksheet, each with its own toggle, operating on a different Named Range. All help gratefully received!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows within range if B? = X
Here is a sub that will unhide if hidden or hide if * for the ACTIVE column
from row 3 to the last row in the column. It probably could be re-written to make the x/* UN necessary by looking directly at col E. Modify to suit Sub hiderowsif() mc = ActiveCell.Column lr = Cells(Rows.Count, mc).End(xlUp).Row rc = Range(Cells(1, 1), Cells(lr, mc)). _ SpecialCells(xlCellTypeVisible).Count If rc < lr Then Rows.Hidden = False Else For i = lr To 3 Step -1 If Cells(i, mc) = "*" Then Rows(i).Hidden = True Next i End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "BEEJAY" wrote in message ... Greetings all: Situation: Formula in each cell in column B: Cell B? has a "x" if no quantity entered in cell E? Cell B? has an "*" if a quantity IS entered in cell E? Range: B788 thru B846 Range Named: T_Box_R Looking for (High Speed) code to Hide all rows with "*" in column B, WITHIN the Specified Named Range ONLY. The code will be operated with a toggle button that will: 1: Activate the hide code 2: Unhide all hidden rows within the specified Named Range There will be numerous incidents of this used within one Worksheet, each with its own toggle, operating on a different Named Range. All help gratefully received!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows within range if B? = X
I forgot to say you need to add a toggle button to whatever worksheet this
code is suppose to operate on, then assign this macro to the toggle button. Correction, this should be the last line: Application.ScreenUpdating = True -- Cheers, Ryan "RyanH" wrote: You will have to put the name of your toggle button in my code. This should work for you. Option Explicit Sub HideRows() Dim cell As Range Application.ScreenUpdating = False If tgButton = True Then For Each cell In Range("T_Box_R") If cell.Value = "*" Then cell.EntireRow.Hidden = True End If Next cell Else Range("T_Box_R").Rows.EntireRow.Hidden = False End If Application.ScreenUpdating = False End Sub -- Cheers, Ryan "BEEJAY" wrote: Greetings all: Situation: Formula in each cell in column B: Cell B? has a "x" if no quantity entered in cell E? Cell B? has an "*" if a quantity IS entered in cell E? Range: B788 thru B846 Range Named: T_Box_R Looking for (High Speed) code to Hide all rows with "*" in column B, WITHIN the Specified Named Range ONLY. The code will be operated with a toggle button that will: 1: Activate the hide code 2: Unhide all hidden rows within the specified Named Range There will be numerous incidents of this used within one Worksheet, each with its own toggle, operating on a different Named Range. All help gratefully received!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows within range if B? = X
Sorry about my tardy response.
I found I had something to learn about "buttons", as well. Based on conclusions made from info in my books and in user groups, I decided to use a "forms" button rather than the active x buttons. I used Mike's code and it works great (AND fast) I do want to work with Ryans and see what modifications it needs to work with forms buttons. Don's code is going to be a major challenge. I want to work on it as well, time permitting. It will have to be adjusted to work on a specified RANGE - I understand it works on the complete active ws, as it is written. Thanks so much for the varied input. As always, much appreciated. "Don Guillett" wrote: Here is a sub that will unhide if hidden or hide if * for the ACTIVE column from row 3 to the last row in the column. It probably could be re-written to make the x/* UN necessary by looking directly at col E. Modify to suit Sub hiderowsif() mc = ActiveCell.Column lr = Cells(Rows.Count, mc).End(xlUp).Row rc = Range(Cells(1, 1), Cells(lr, mc)). _ SpecialCells(xlCellTypeVisible).Count If rc < lr Then Rows.Hidden = False Else For i = lr To 3 Step -1 If Cells(i, mc) = "*" Then Rows(i).Hidden = True Next i End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "BEEJAY" wrote in message ... Greetings all: Situation: Formula in each cell in column B: Cell B? has a "x" if no quantity entered in cell E? Cell B? has an "*" if a quantity IS entered in cell E? Range: B788 thru B846 Range Named: T_Box_R Looking for (High Speed) code to Hide all rows with "*" in column B, WITHIN the Specified Named Range ONLY. The code will be operated with a toggle button that will: 1: Activate the hide code 2: Unhide all hidden rows within the specified Named Range There will be numerous incidents of this used within one Worksheet, each with its own toggle, operating on a different Named Range. All help gratefully received!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows within range if B? = X
I don't believe there is a Forms Control Toggle Button, I could be wrong
though. If you only have one control on the worksheet the ActiveX Toggle Button will give you more flexibilty and won't affect performance. I think performance is affected when you have multiple ActiveX controls. Add a Toggle Button to whatever worksheet you are trying to hide the rows. Then place this code in the Toggle Buttons Click Event which should be in the worksheets module. Option Explicit Private Sub ToggleButton1_Click() Dim cell As Range Application.ScreenUpdating = False If ToggleButton1 = True Then For Each cell In Range("T_Box_R") If cell.Value = "*" Then cell.EntireRow.Hidden = True End If Next cell Else Range("T_Box_R").Rows.EntireRow.Hidden = False End If Application.ScreenUpdating = True End Sub Note: If the toggle button happens to be located in the rows you are hidding you may want to set the Format Control to Don't Size or Move with Cells. -- Cheers, Ryan "BEEJAY" wrote: Sorry about my tardy response. I found I had something to learn about "buttons", as well. Based on conclusions made from info in my books and in user groups, I decided to use a "forms" button rather than the active x buttons. I used Mike's code and it works great (AND fast) I do want to work with Ryans and see what modifications it needs to work with forms buttons. Don's code is going to be a major challenge. I want to work on it as well, time permitting. It will have to be adjusted to work on a specified RANGE - I understand it works on the complete active ws, as it is written. Thanks so much for the varied input. As always, much appreciated. "Don Guillett" wrote: Here is a sub that will unhide if hidden or hide if * for the ACTIVE column from row 3 to the last row in the column. It probably could be re-written to make the x/* UN necessary by looking directly at col E. Modify to suit Sub hiderowsif() mc = ActiveCell.Column lr = Cells(Rows.Count, mc).End(xlUp).Row rc = Range(Cells(1, 1), Cells(lr, mc)). _ SpecialCells(xlCellTypeVisible).Count If rc < lr Then Rows.Hidden = False Else For i = lr To 3 Step -1 If Cells(i, mc) = "*" Then Rows(i).Hidden = True Next i End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "BEEJAY" wrote in message ... Greetings all: Situation: Formula in each cell in column B: Cell B? has a "x" if no quantity entered in cell E? Cell B? has an "*" if a quantity IS entered in cell E? Range: B788 thru B846 Range Named: T_Box_R Looking for (High Speed) code to Hide all rows with "*" in column B, WITHIN the Specified Named Range ONLY. The code will be operated with a toggle button that will: 1: Activate the hide code 2: Unhide all hidden rows within the specified Named Range There will be numerous incidents of this used within one Worksheet, each with its own toggle, operating on a different Named Range. All help gratefully received!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows within range if B? = X
Ryan: I'm probably using the wrong terminology.
I am using the button, from the forms toolbar. When I attach Mikes Code, it nicely "toggles", as I require it. Since I will have 12 or more of these buttons on each sheet, I felt it was best to use the forms button, rather than the Active X Controls (based on my readings). Ryan - Again thanks for your extra input. I think I have another up-coming project that your latest post hits right on the head. "RyanH" wrote: I don't believe there is a Forms Control Toggle Button, I could be wrong though. If you only have one control on the worksheet the ActiveX Toggle Button will give you more flexibilty and won't affect performance. I think performance is affected when you have multiple ActiveX controls. Add a Toggle Button to whatever worksheet you are trying to hide the rows. Then place this code in the Toggle Buttons Click Event which should be in the worksheets module. Option Explicit Private Sub ToggleButton1_Click() Dim cell As Range Application.ScreenUpdating = False If ToggleButton1 = True Then For Each cell In Range("T_Box_R") If cell.Value = "*" Then cell.EntireRow.Hidden = True End If Next cell Else Range("T_Box_R").Rows.EntireRow.Hidden = False End If Application.ScreenUpdating = True End Sub Note: If the toggle button happens to be located in the rows you are hidding you may want to set the Format Control to Don't Size or Move with Cells. -- Cheers, Ryan "BEEJAY" wrote: Sorry about my tardy response. I found I had something to learn about "buttons", as well. Based on conclusions made from info in my books and in user groups, I decided to use a "forms" button rather than the active x buttons. I used Mike's code and it works great (AND fast) I do want to work with Ryans and see what modifications it needs to work with forms buttons. Don's code is going to be a major challenge. I want to work on it as well, time permitting. It will have to be adjusted to work on a specified RANGE - I understand it works on the complete active ws, as it is written. Thanks so much for the varied input. As always, much appreciated. "Don Guillett" wrote: Here is a sub that will unhide if hidden or hide if * for the ACTIVE column from row 3 to the last row in the column. It probably could be re-written to make the x/* UN necessary by looking directly at col E. Modify to suit Sub hiderowsif() mc = ActiveCell.Column lr = Cells(Rows.Count, mc).End(xlUp).Row rc = Range(Cells(1, 1), Cells(lr, mc)). _ SpecialCells(xlCellTypeVisible).Count If rc < lr Then Rows.Hidden = False Else For i = lr To 3 Step -1 If Cells(i, mc) = "*" Then Rows(i).Hidden = True Next i End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "BEEJAY" wrote in message ... Greetings all: Situation: Formula in each cell in column B: Cell B? has a "x" if no quantity entered in cell E? Cell B? has an "*" if a quantity IS entered in cell E? Range: B788 thru B846 Range Named: T_Box_R Looking for (High Speed) code to Hide all rows with "*" in column B, WITHIN the Specified Named Range ONLY. The code will be operated with a toggle button that will: 1: Activate the hide code 2: Unhide all hidden rows within the specified Named Range There will be numerous incidents of this used within one Worksheet, each with its own toggle, operating on a different Named Range. All help gratefully received!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for Excel 2007 to hide rows based on sum of several rows not ina range | Excel Programming | |||
Hide rows outside a known range of rows | Excel Programming | |||
Hide rows within a range IF... | Excel Programming | |||
Hide all rows within a range except one? | Excel Discussion (Misc queries) | |||
Hide blank rows in a range | Excel Programming |