Hide rows within range if B? = X

[BEEJAY]

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!!

 

[RyanH]

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

 

[Mike H]

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

 

[Don Guillett]

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

 

[RyanH]

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

 

[BEEJAY]

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.

 

[RyanH]

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.

 

[BEEJAY]

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.