Turn off Autofilter but leave rows hidden.

[GollyJer]

Is there any way to turn off an Autofilter and leave the rows hidden?

I use the following method for showing hiding rows. Its very fast and
doesn't do anything if not needed (all need to show are showing and all need
to hide are hidden). There's a helper column (B) that contains an error,
"N/A", or a zero "0".

Screen updating and calculation are turned off when these methods run.

--------------------------------------------------------------------------------------
Sub SetRowVisibility()

Dim rowsToCheck As Range
With ActiveSheet
Set rowsToCheck = .Range(Range("B7"), Range("B7").End(xlDown))
End With

Dim needToShow As Range, needToShow_Showing As Range
Dim needToHide As Range, needToHide_Showing As Range

Set needToShow = rowsToCheck.SpecialCells(xlCellTypeFormulas, xlNumbers)
Set needToHide = rowsToCheck.SpecialCells(xlCellTypeFormulas, xlErrors)

On Error Resume Next
Set needToShow_Showing = needToShow.Offset(0,
1).SpecialCells(xlCellTypeVisible)
Set needToHide_Showing = needToHide.Offset(0,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

'Hide only if needed and not hidden.
If Not needToHide_Showing Is Nothing Then
needToHide_Showing.EntireRow.Hidden = True
End If

'Show only if needed and not showing.
If Not needToShow Is Nothing Then
If needToShow.Count <> needToShow_Showing.Count Then
needToShow.EntireRow.Hidden = False
End If
End If

End Sub
--------------------------------------------------------------------------------------


I'd like to use the Autofilter but this is the best I can do. It always has
to filter the data to run.
--------------------------------------------------------------------------------------
Sub SetRowVisibility2()
Dim needToShow As Range, needToHide As Range

With ActiveSheet.Range(Range("B7"), Range("B7").End(xlDown))

On Error Resume Next
.AutoFilter Field:=1, Criteria1:="0"
Set needToShow = .SpecialCells(xlCellTypeVisible)

.AutoFilter Field:=1, Criteria1:="#N/A"
Set needToHide = .SpecialCells(xlCellTypeVisible)

ActiveSheet.AutoFilterMode = False

needToShow.EntireRow.Hidden = False
needToHide.EntireRow.Hidden = True

End With

End Sub
--------------------------------------------------------------------------------------

The ideal solution would be to filter using the Autofilter, turn off the
Autofilter and leave the filtered cells hidden. Possible?

Thanks,
Jeremy

 

[Dave Peterson]

How about leaving the filter active, but hiding the filter arrows?

Debra Dalgleish shows how:
http://contextures.com/xlautofilter03.html#Hide

 

[GollyJer]

Thanks Dave. I have tried that. It still looks weird to the user because
of the blue row numbers (vs. the normal black). If speed becomes the
absolute decision maker I think using autofilter and hiding the filter arrow
would be the solution.

 

[Dave Peterson]

I guess you could loop through the values in column B:

Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))

For Each myCell In myRng.Cells
If IsError(myCell.Value) Then
myCell.EntireRow.Hidden = True
Else
Select Case LCase(myCell.Text)
Case "n/a", "0"
myCell.EntireRow.Hidden = True
Case Else
myCell.EntireRow.Hidden = False
End Select
End If
Next myCell
End With

Did you mean "#n/a" or "n/a"????

But it's not possible to remove the autofilter while keeping those filtered rows
hidden.