How to identify and modify rows hidden by autofilter

In Make a copy of a sheet and respect autofilter I covered a one liner to make a copy only visible rows in a worksheet. Let’s say that we want to, say, change the format or modify the values in the visible (or invisible) rows of a worksheet with autoFilter applied. Here’s an example where we change the background color of the visible and invisible rows of an autofiltered worksheet.

Page Content hide
1 Code
2 WalkThrough
3 Summary

Code

Here we are going to mark hidden rows in red, non hidden in yellow.

Public Sub respectFilter()
    Dim ds2 As New cDataSet, dr As cDataRow
    With ds2.populateData(wholeSheet("mercadoLibre"), , , , , , True, , , , True)
        For Each dr In .rows
            If dr.hidden Then
                dr.Where.Interior.Color = vbRed
            Else
                dr.Where.Interior.Color = vbYellow
            End If
        Next dr
    End With
End Sub

WalkThrough

In Make a copy of a sheet and respect autofilter I showed the call stack to populateData to respect filters. In this case each row has the .hidden property set to record whether the row was hidden or visible.

This gives this result, after I autofiltered the input table on ‘platinum status’

Summary

Take a look at One Liners for more tips like this. In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available.

You can this example in the cDataSet.xlsm from Downloads