VBA - InterSect, filtered rows, CTRL+D

Question

I'm handling Change event in Excel using intersect like this:

Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range
    Set WatchRange = Range("B2:B65536")
    Set IntersectRange = Intersect(Target, WatchRange)

    If IntersectRange Is Nothing Then
        'Do Nothing
    Else
        For Each b In Target.Rows
            Range("A" & b.Row).Value = "*"
        Next
    End If
End Sub

When I change something in column B, it writes * to column A. And it works great.

Problem happens when I set the filter and copy some value to filtered rows using CTRL+D. This is my test table:

Filter only text in column C:

And copy number 100 using CTRL+D to all visible rows (column B):

When I cancel the filter, all the rows are marked with *:

It wouldn't be a problem in small table, but it takes about 10sec in table with about 1000 rows.

Is it possible to skip hiden files somehow?

score 1 · Accepted Answer · answered Nov 02 '16 at 12:34

Use .EntireRow.Hidden to figure out if particular cell is filtered or not. Your code would look like:

Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range
    Set WatchRange = Range("B2:B65536")
    Set IntersectRange = Intersect(Target, WatchRange)

    If IntersectRange Is Nothing Then
        'Do Nothing
    Else
        For Each b In Target
            If b.EntireRow.Hidden = False Then
                Range("A" & b.Row).Value = "*"
            End If
        Next
    End If
End Sub

Thank you very much, that's exactly what I needed! – gaffcz Nov 02 '16 at 13:29 — gaffcz, Nov 02 '16 at 13:29

VBA - InterSect, filtered rows, CTRL+D

1 Answers1