Using AutoFilter in Excel Macro with Dim LastRow

manster_mg · Jul 9th 2021

I want the macro to format cells in the worksheet when the value in column A equals "30", "42" or "7". Below is the code I recorded but since my data changes day to day this will not work. I had a macro in the past that worked with the AutoFilter and it was so much more simpler but I lost it. Any help would be appreciated.

Code

Sub Auto_Filter_Format()

Dim LastRow As Long
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Range("A1").AutoFilter
    Range("$A$1:$AA$" & LastRow).AutoFilter Field:=1, Criteria1:=Array( _
        "30", "42", "7"), Operator:=xlFilterValues

'How to find first cell in AutoFilter Range? In this case it is cell A5
    Range("A5").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
'How to find first cell in AutoFilter Range? In this case it is cell K5
    Range("K5").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
        
    ActiveSheet.Range("$A$1:$AA$" & LastRow).AutoFilter Field:=1
    
    
End Sub

Display More

**royUK** · Jul 10th 2021

Why don't you use Conditional Formatting?

manster_mg · Jul 12th 2021

I could use conditional formatting but I have 3 different criteria that don't work with < than, > than or between because there are multiple range values from 1 to 67. What's wrong with using AutoFilter? I've seen very good vba code that uses the auto filter method.

**royUK** · Jul 13th 2021

AutoFilter is automatic.

VBA will not be as efficient as any inbuilt Excel feature.

You can use the OR Function in Conditional Formatting to have more than one condition.

manster_mg · Jul 15th 2021

I don't understand the difference between "Automatic" and an inbuilt Excel feature. I would rather have used a solution using AutoFilter but since that seems to be inefficient would you mind showing me how to use OR in the code below for conditional formatting? I need to format cells in column C with values 7, 10, and 42. Thanks!

Code

Sub WOS()

Dim LastRow As Long

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Format Column M Position WOS
    Range("C2:C" & LastRow).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=7"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With

End Sub

Display More

Using AutoFilter in Excel Macro with Dim LastRow

Participate now!

Share

Tags