SUBTOTAL and Filter Mode

**efernandes67** · 07-31-2010, 10:45 PM

HI,

See the file in attach.

I have two tables in the same page that the number of rows is dynamic.
Table2 is always 3 rows below Table1 regardless the number of rows of Table1.

1) I need to calculate the total number of rows of each table and put the result in "E4" an "E15" regardless the number of rows of each table.

2) If Iuse a filter or in the range("B6:E6") or range("B17:E17") and change it, I need to values in "E4" and "E15" to be updated.

Here is some code that I was trying to use:


With Application.WorksheetFunction
     Range("E4") = .Subtotal(3, Range("B7:B" & .Match("Table2", Range("B:B"), 0) - 4))

End With

Regards,
Elio Fernandes

**ajaykgarg** · 08-02-2010, 06:35 AM

Does this help?

    ' assuming first table always starts at B6
    Dim first_table_end As Integer
    Dim first_table_rows As Integer
    
    Dim second_table_start As Integer
    Dim second_table_end As Integer
    Dim second_table_rows As Integer
    
    first_table_end = Range("B6").End(xlDown).Row
    first_table_rows = first_table_end - 6 ' assuming first table always starts at B6
    
    Range("E4") = Range("B7:B" & first_table_end).SpecialCells(xlCellTypeVisible).Count ' assuming first table always starts at B6
        
    second_table_start = first_table_end + 7 'Table2 is always 3 rows below Table1, plus 4 rows of headers
    second_table_end = Range("B" & second_table_start).End(xlDown).Row
    second_table_rows = second_table_end - second_table_start ' assuming first table always starts at B6

    Range("E15") = Range("B" & second_table_start & ":B" & second_table_end).SpecialCells(xlCellTypeVisible).Count  ' assuming first table always starts at B6

hth
Ajay

**efernandes67** · 08-04-2010, 08:03 AM

Hi Ajay,

Your code helps but its not enough because, I need the values in "E4" and "E15" to change when I used the Filter.

Can you give me an hand?

Regards,
Elio Fernandes

Originally Posted by ajaykgarg

Does this help?

    ' assuming first table always starts at B6
    Dim first_table_end As Integer
    Dim first_table_rows As Integer
    
    Dim second_table_start As Integer
    Dim second_table_end As Integer
    Dim second_table_rows As Integer
    
    first_table_end = Range("B6").End(xlDown).Row
    first_table_rows = first_table_end - 6 ' assuming first table always starts at B6
    
    Range("E4") = Range("B7:B" & first_table_end).SpecialCells(xlCellTypeVisible).Count ' assuming first table always starts at B6
        
    second_table_start = first_table_end + 7 'Table2 is always 3 rows below Table1, plus 4 rows of headers
    second_table_end = Range("B" & second_table_start).End(xlDown).Row
    second_table_rows = second_table_end - second_table_start ' assuming first table always starts at B6

    Range("E15") = Range("B" & second_table_start & ":B" & second_table_end).SpecialCells(xlCellTypeVisible).Count  ' assuming first table always starts at B6

hth
Ajay

**ajaykgarg** · 08-04-2010, 01:11 PM

Hello Elio

I thought Worksheet_Calculate() event will be fired when filter criteria is changed, but that is not true.
I hope someone on the forum can tell us which event is fired when filter criteria is changed.
You need to copy above code in that event.

thx
Ajay