I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

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.


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
                dr.Where.Interior.Color = vbYellow
            End If
        Next dr
    End With
End Sub


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’


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