Recalculation problem

[Guest]

Hi!

I am trying to calculate the number of visible rows in a filtered sheet.
this sheet is all the time accumulate more and more rows, and the code that
i use can not recalculate the number of visible rows unless i am selecting
the relevant cells and double click them. (first calculation is working)

The code is base on Tom Ogilvy's code found somewhere.

Dim tdy As Date
Dim Sum1 As Integer
Dim wss As Worksheet
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
Set wss = Worksheets("Sheet1")
wss.Select
tdy = Format(Date, "Short Date")
Selection.AutoFilter Field:=6, Criteria1:=tdy
Selection.AutoFilter Field:=7, Criteria1:="Day"
num1 = rng.Columns(2).SpecialCells(xlCellTypeVisible).Count - 1

Thanks for your suggestion

Eli

 

[Guest]

The macro will always give the number of visible rows (well visible rows -1)
but it sounds like when you manually change the number of visible rows by
adding data you aren't running the macro again to recalculate until you
double click the sheet.

Maybe you need to consider running the macro dependant on another event.

Mike

 

[Guest]

Mike,

This macro is included in both Initialize and click events.
P.S. - the double click is done on the specific cell (the relevant one for
the calculation).

Eli