Run a macro on filtered/hidden rows?

Question

I'm new to the group/VBA so please forgive me if I'm not as concise as I should be.

I've got a macro that adds a formula to a range of cells. However, the range has filters and when I apply one and run the code, the formula is only added to visible cells and not the rows that are filtered out.

Is there a way to get the code to ignore applied filters? I'd prefer not to use ShowAllData to remove the filters so as users are working with the file they are able to keep their place if/when they run the code.

Function RefreshFormulas()

Dim ws As Worksheet
Set ws = ActiveSheet

Dim used As Range
Set used = ws.UsedRange

Dim LastRow As Integer
LastRow = used.Row + used.Rows.Count - 1

ws.Range("A5:A" & LastRow).Formula = "=IF(ISBLANK(RC6),"""",'Report Setup'!R9C2)"

End Function

Say my range has 3,000 rows. When not filtered, the code above will fill the used range in column A with the formula in all 3,000 rows. However, if I was to filter a column and now only 500 rows are visible, the formula would only be added to those 500 rows. I'd like the code to be run on all 3,000 rows regardless of filters.

Why not turn off the filters, add the formula and then turn them back on. You can always record your place before doing so. — SJR, Oct 15 '19 at 16:38
Side note: Use `Long` instead of `Integer`, and it looks like that should be `.FormulR1C1` instead of `.Formula`. — BigBen, Oct 15 '19 at 16:42
@SJR - I thought about that but just wasn't sure it was possible to record your place, remove the filters, and then reapply and go back to where you were. I'll look into it a bit more! Thank you! — Tyler, Oct 15 '19 at 17:11
@BigBen - I switched out Integer with Long but same result. However, given that I'm still a novice I'll leave it as long and assume there's a good reason behind it lol. Thanks! — Tyler, Oct 15 '19 at 17:11
Yes, you are right, it will not change the result, it was just a side note. [Here's](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) the reasoning behind it, if you are curious and have time! — BigBen, Oct 15 '19 at 17:12
@TylerWebb there're several posts out there which state that, in its current format, VBA will inherently convert `Integer` to `Long`, which takes more effort than just defining as `Long` in the first part. — Cyril, Oct 15 '19 at 17:13
Assign the activecell to a variable and then afterwards goto that cell. Or better still, save a custom view and I think that might preserve your filter settings. — SJR, Oct 15 '19 at 17:30
Nice one - you can post your code as an answer to your own question. — SJR, Oct 15 '19 at 18:28
Please place answers in Answer blocks. Please don't add answers to questions. Later, you can accept your own Answer. Also see [How does accepting an answer work?](https://meta.stackexchange.com/q/5234/173448) — jww, Dec 11 '19 at 20:29

score 0 · Accepted Answer · answered Jan 15 '20 at 17:08

Thanks all! I was able to find a way to save the filter settings and then run my code and reapply the settings. I've added the code below (not my code but works well) in case anyone is interested in seeing what I was able to find.

Sub FormulaRefresh()
'Macro to add formulas to all rows within the range. As users add/remove rows, the formulas will dynamically populate.

Dim ws As Worksheet
Set ws = ActiveSheet

Dim used As Range
Set used = ws.UsedRange

Dim LastRow As Long
LastRow = used.Row + used.Rows.Count - 1

Dim FilterArray()
Dim CurrentFiltRange As String
Dim Col As Long

    ' Capture AutoFilter settings
    With ws.AutoFilter
        CurrentFiltRange = .Range.Address
        With .Filters
            ReDim FilterArray(1 To .Count, 1 To 3)
            For f = 1 To .Count
                With .Item(f)
                    If .On Then
                        FilterArray(f, 1) = .Criteria1
                        If .Operator Then
                            FilterArray(f, 2) = .Operator
                            FilterArray(f, 3) = .Criteria2
                        End If
                    End If
                End With
            Next f
        End With
    End With
    'Remove AutoFilter
    ws.AutoFilterMode = False

 'Formula to be entered    
 ws.Range("A5:A" & LastRow).FormulaR1C1 = "=IF(ISBLANK(RC6),"""",'Report Setup'!R9C2)"

    ' Restore Filter settings

    For Col = 1 To UBound(FilterArray(), 1)
        If Not IsEmpty(FilterArray(Col, 1)) Then
            If FilterArray(Col, 2) Then
                ws.Range(CurrentFiltRange).AutoFilter field:=Col, _
                Criteria1:=FilterArray(Col, 1), _
                Operator:=FilterArray(Col, 2), _
                Criteria2:=FilterArray(Col, 3)
            Else
                ws.Range(CurrentFiltRange).AutoFilter field:=Col, _
                Criteria1:=FilterArray(Col, 1)
            End If
        End If
    Next Col

If ws.AutoFilterMode = False Then ws.Range("4:4").AutoFilter 
'If a filter isn't applied when I run they will disappear so I added a quick check at the end to add them back to row 4 if they are gone.

End Sub

I'm running this with different formulas in 12 columns on around 5,000 rows doing a few other things and it runs in under 3 seconds so seems to be pretty quick!

Run a macro on filtered/hidden rows?

1 Answers1