You dont need VBA, use subtotal it only examines visible cells...i.e,
use:

=SUBTOTAL(2,A1:A9), with A1:A9 being the range of all possible
cells...if you look at help you can see the other uses for subtotal
(i.e. the 2 tells it count, but you can have subtotal do things like
sum up the visible cells)