Totals rows shouldn't become invisible when filtering

[John]

I have 2 rows of totals at the bottom of my database (separated from the DB
by an empty row). When filtering with AutoFilter, the totals rows will
sometimes become hidden. I added the following procedure in my worksheet
calculate event in an attemt to remedy this problem:

Private Sub Worksheet_Calculate()
'Prevent Totals Row from being hidden when filtering
On Error Resume Next
If wshDatabase.Range("TotalsRows").EntireRow.Hidden = True Then
wshDatabase.Range("TotalsRows").EntireRow.Hidden = False
Exit Sub
On Error GoTo 0
End If
End Sub

The problem is, this sub causes Excel to crash. Can someome recommend a
better sub to keep the totals rows unhidden when filtering?

 

[Paul Robinson]

Hi
You will need to be more precise about the range you are filtering, so
that the total rows are excluded. Then you won't need the
worksheet_Calculate code.
What is your code that does the filter?
regards
Paul

 

[James Ravenswood]

An easy way is to not include the "totals" rows in the filter in the
first place. For example, if the header row is row#1 and the data is
in rows#2-99 and row#100 is blank and row#101 contains the totals,
then:


Sub Macro2()
Rows("1:99").Select
Selection.AutoFilter
End Sub

 

[Don Guillett Excel MVP]

I have 2 rows of totals at the bottom of my database (separated from the DB
by an empty row). When filtering with AutoFilter, the totals rows will
sometimes become hidden. I added the following procedure in my worksheet
calculate event in an attemt to remedy this problem:

Private Sub Worksheet_Calculate()
   'Prevent Totals Row from being hidden when filtering
    On Error Resume Next
    If wshDatabase.Range("TotalsRows").EntireRow.Hidden = True Then
        wshDatabase.Range("TotalsRows").EntireRow.Hidden = False
        Exit Sub
    On Error GoTo 0
    End If
 End Sub

The problem is, this sub causes Excel to crash. Can someome recommend a
better sub to keep the totals rows unhidden when filtering?

Why not just have your autofilter exclude the last two rows