PDA

View Full Version : [SOLVED] Best way to hide rows depending on cell value?



davidov
10-01-2017, 12:21 PM
I have several sets of rows to hide/unhide based on certain cell values.

I've been identifying rows by means of a hidden marker in column A (in this case 'x') because, having hidden one set of rows, the row numbers become changed and can't be reused to hide another set. I have no problem with having these markers in the worksheet.

The method below works to hide/unhide the rows - problem is it's slow - it visibly hides/unhides the rows one by one - you can almost hear cogs turning. Is there a more sophisticated way of doing this, hopefully instantaneously?


Sub ConditionalHideRows()

If Range("B2").Value = "Case 1" Then
BeginRow = 1
EndRow = 250
ChkCol = 1
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "x" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End If
If Range("B2").Value <> "Case 1" Then
BeginRow = 1
EndRow = 250
ChkCol = 1
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "x" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End If
End Sub

mdmackillop
10-01-2017, 01:19 PM
If Column A contains only your check values

Sub Test()
Columns(1).SpecialCells(2).EntireRow.Hidden = (Range("B2").Value = "Case 1")
End Sub

davidov
10-01-2017, 09:56 PM
That's faster, thanks. Instantaneous was probably a tall order.

That code hides all rows containing all check values though. I have several sets of rows identified by various hidden check values (say 'x', 'y', or 'z') in column A (which is otherwise empty). Depending on various cell inputs, I need to hide/unhide one or other of these sets, and usually more than one set at a time.

I'm wondering how to add a check value identifier into that line...

Paul_Hossler
10-02-2017, 02:59 PM
Not as terse




Option Explicit
Sub drv()
Call HideRowIfMarker("x")
End Sub

Private Sub HideRowIfMarker(s As String)
Dim c As Range

Application.ScreenUpdating = False
ActiveSheet.Rows.Hidden = False

On Error GoTo NiceExit
For Each c In ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants).Cells
c.EntireRow.Hidden = (c.Value = s)
Next
NiceExit:
Application.ScreenUpdating = True
End Sub

mdmackillop
10-02-2017, 04:11 PM
Give this a try

Sub Test()
Set R = Columns(1)
f = InputBox("Filter data; comma separated, e.g. z or x,y" & vbCr & "Leave blank to clear")
R.AutoFilter
Select Case Len(f)
Case 0
Exit Sub
Case 1
Select Case f
Case "x"
crit = Array("y", "z", "=")
Case "y"
crit = Array("x", "z", "=")
Case "z"
crit = Array("x", "y", "=")
End Select
Case 3
Select Case f
Case "x,y"
crit = Array("z", "=")
Case "x,z"
crit = Array("y", "=")
Case "y,z"
crit = Array("x", "=")
End Select
Case Else
crit = Array("=")
End Select
R.AutoFilter Field:=1, Criteria1:=Array(crit), Operator:=xlFilterValues
End Sub

davidov
10-03-2017, 10:52 AM
Thanks for the two ingenious suggestions. The xlFilterValues method is the fastest - fairly instantaneous - so that answers my basic question.

I'm not out of the woods yet though as I'm trying to do without an InputBox. I just want the rows to hide when the user fills in certain fields while going through the form so I'm now trying a Worksheet_Calculate event.

This is where I currently am. Only the last of the three hide processes works. If I switch them round, it's always the last one that works - the first two always do nothing. Some kind of logic problem...



Private Sub Worksheet_Calculate()

Application.EnableEvents = False
Application.ScreenUpdating = False

'Hides x rows if Open Access model, unhides x rows if not
Set r = Range("A1:A250")
If Range("BusinessModel").Value = 4 Then
r.AutoFilter
crit = Array("y", "z", "=")
r.AutoFilter Field:=1, Criteria1:=Array(crit), Operator:=xlFilterValues
Else: r.AutoFilter
crit = Array("x")
End If

'Hides y rows if Gross Profit method, unhides y rows if not
Set r = Range("A1:A250")
If Range("G142").Value = "GP" Then
r.AutoFilter
crit = Array("x", "z", "=")
r.AutoFilter Field:=1, Criteria1:=Array(crit), Operator:=xlFilterValues
Else: r.AutoFilter
crit = Array("y")
End If

'Hides z rows if Net Profit method, unhides z rows if not
Set r = Range("A1:A250")
If Range("G142").Value = "NP" Then
r.AutoFilter
crit = Array("x", "y", "=")
r.AutoFilter Field:=1, Criteria1:=Array(crit), Operator:=xlFilterValues
Else: r.AutoFilter
crit = Array("z")
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

By the way: the worksheet has 250 rows - the rest are permanently hidden.

mdmackillop
10-03-2017, 01:22 PM
Else: r.AutoFilter crit = Array("x")
End If
This is wrong
r.AutoFilter - This clears the filter so crit = Array("x") does nothing

If you want to ELSE to filter on x etc. then



r.AutoFilter
If Range("BusinessModel").Value = 4 Then
crit = Array("y", "z", "=")
Else
crit = Array("x")
End If
r.AutoFilter Field:=1, Criteria1:=Array(crit), Operator:=xlFilterValues

mdmackillop
10-03-2017, 01:24 PM
Can you post a workbook with sample data/layout

davidov
10-03-2017, 10:47 PM
r.AutoFilter
If Range("BusinessModel").Value = 4 Then
crit = Array("y", "z", "=")
Else
crit = Array("x")
End If
r.AutoFilter Field:=1, Criteria1:=Array(crit), Operator:=xlFilterValues

That doesn't work for me. I wanted ELSE to unfilter (ie. make visible) the x rows if the cell value isn't 4. The code in post 6 works for that (though I was only guessing) - at least when run as individual subs. When run in a Worksheet_Calculate or Worksheet_Change event I now find it's not working - weird because I'm pretty sure I had it going yesterday - maybe some quantum effect of the weather.

Afraid I can't post the actual spreadsheet. The attached shows the basic requirement, but you probably already got that.

mdmackillop
10-04-2017, 03:05 AM
Slightly different approach

Private Sub Worksheet_Calculate() Dim x, Crit
Application.EnableEvents = False
Application.ScreenUpdating = False


Set r = Range("A1:A25")
If [B1] = "ShowAll" Then
r.AutoFilter
Exit Sub
End If


x = [I2] & "-" & [H2] 'Linked cells


Select Case x
Case "1-1" 'Hide -,z
Crit = Array("x", "y", "=")
Case "1-2" 'Hide -,y
Crit = Array("x", "z", "=")
Case "2-1" 'Hide x,z
Crit = Array("y", "=")
Case "2-2" 'Hide x,y
Crit = Array("z", "=")
End Select


r.AutoFilter
r.AutoFilter Field:=1, Criteria1:=Array(Crit), Operator:=xlFilterValues


Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

davidov
10-04-2017, 10:15 AM
Nice. That nails it, though I had to put the "Dim x, Crit" bit on a separate line to get it to trigger.
We should call this the "David Coleman teleprinter method" as it looks like a bunch of football scores.

Really appreciate the interest you took in this.
Cheers.

mdmackillop
10-04-2017, 10:34 AM
I had to put the "Dim x, Crit" bit on a separate line
I'm afraid that's a site code formatting issue; usually, I spot it.

davidov
10-05-2017, 10:01 PM
Just to report: they loved this trick of hiding sets of unneeded rows so much that they have asked to me to add another case.
I had three either/or cases which meant 16 'football results' (permutations) in the code. That was fine. Adding another case is going to lead to so many more that, even though it works, I think it is getting impractical.

Also, I find that the r.autofilter method doesn't work when the worksheet is protected (even if you check 'Use Autofilter' when protecting:dunno). That is almost a deal-breaker, but unless there is another instantaneous hiding method, I'll probably live with it.

mdmackillop
10-06-2017, 03:37 AM
Adding another case is going to lead to so many more that, even though it works, I think it is getting impractical
I thought about that as I was writing the code and considered removing values from a comprehensive array.
Here's a clumsy version of that concept. I'm sure with more work it could be done in code using arrays, rather than the worksheet "help" areas.

davidov
10-08-2017, 02:10 AM
It looks sophisticated to me, but it doesn't work as a worksheet_calculate event? The filtering would have to be automatic for my purpose.

The filtering is actually working fine for me at the moment. The additional case meant only a couple of changes to the crit arrays, and by means of simple IFs in column A, the marker can be changed to allow more permutations.

The only problem for me now is that r.autofilter doesn't work with the worksheet protected. Haven't yet investigated if there is a way round this.

mdmackillop
10-08-2017, 03:24 AM
Add a Calculate event to call it.

Private Sub Worksheet_Calculate()
Call MultiFilter
End Sub


Protection code added to Workbook module should fix protected sheet issue

Private Sub Workbook_Open()

'If you have different passwords


'for each Worksheet.


Sheets(1).Protect Password:="Secret", _
UserInterFaceOnly:=True


'Repeat as needed.


End Sub

davidov
10-08-2017, 07:05 AM
Efficacious! Many thanks.