Filter out records with VBA

How to write a VBA to quickly filter out those records with column C = "Yes" ?

Tks
AXISHKAsked:
Who is Participating?
 
Rodney EndrigaConnect With a Mentor Data AnalystCommented:
Here is VBA code you can use. You can update the COLUMN reference if your data is elsewhere:

Sub EE_FilterYESinColumn()
Dim rng1 As Range

'Change the column<<C>> if data is elsewhere
Set rng1 = ActiveSheet.Range("C1:C" & ActiveSheet.UsedRange.Rows.Count)

For Each cell In rng1
    cell.AutoFilter Field:=1, Criteria1:="Yes"
Next
End Sub
0
 
magentoConnect With a Mentor Commented:
Hi ,

Try this please. I used record macro.

Sub Filter()

    Columns("C:C").Select
    Selection.AutoFilter
    ActiveSheet.Range("$C$1:$C$1048576").AutoFilter Field:=1, Criteria1:="Yes"
End Sub

Open in new window


Thanks
0
 
Rgonzo1971Connect With a Mentor Commented:
HI,

If you want to filter Yes out then try

Sub Macro1()
    ActiveSheet.Range(Range("C1"), Range("C" & Cells.Rows.Count).End(xlUp)).AutoFilter _
            Field:=1, Criteria1:="<>*Yes*"
End Sub

Open in new window

Regards
0
 
AXISHKAuthor Commented:
Thank, but how to check the last row with column = "Yes" ??
Actually, I want to keep those record with column = Yes only. Tks
0
 
AXISHKAuthor Commented:
Tks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.