Godders199
Active Member
- Joined
- Mar 2, 2017
- Messages
- 313
- Office Version
- 2013
Hello, I have the following VBA that hides all columns notin the date range in A1-b1
In cell jd2 I want to just put in the formula to count thevisible cells with an “A” in. I havetried
=AGGREGATE(3,3,C2:JC2)
But this is giving me the overall total for the row, not justof the visible cells, how do I change the formula to ignore the hidden columns?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dt As Range
If Intersect(Target,Range("a1:b1")) Is Nothing Then Exit Sub
Columns("c:jc").Hidden = False
For Each dt InRange("c1:jc1")
If dt.Value <[a1] Or dt.Value > [b1] Then Columns(dt.Column).Hidden = True
Next dt
End Sub
=AGGREGATE(3,3,C2:JC2)
But this is giving me the overall total for the row, not justof the visible cells, how do I change the formula to ignore the hidden columns?
Last edited by a moderator: