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. 

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

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.







Comments