Macro to filter 2 columns and then delete remaining rows

**jimmisavage** · 06-12-2014, 08:29 AM

Hi everyone,
I'm hoping someone might be able to help me.

Column C has a possible text of "Enrolled", "Failed" or "Discontinued"
Column E has possible text including:
"Visit 1 (Week 1)"
"Visit 1 (week 2)" etc
"Visit 2 (week 1)"
"Visit 2 (Week 2)" etc
all the way up to "Visit 22 (Week 52)" as well as other text which is not related to my question such as "Early Termination".
I need to build a macro to filter column C for "Enrolled" and then filter column E for everything that starts with "Visit 7" up to "Visit 22". I then need to delete all those remaining rows (Enrolled Visit 7+) and then remove the filters to show everything remaining.

I hope this makes sense to someone! Feel free to let me know if you need any more information.

Many thanks
Stuart

**Bernie Deitrick** · 06-12-2014, 10:11 AM

This assumes headers in Row 1, and that you want to delete "Enrolled" and "Week 7+" not the other way around. Try it on a copy of your worksheet first...

Sub TestMacro()
    Dim lngR As Long
    Set lngR = Cells(Rows.Count, "C").End(xlUp).Row
    
    Range("F:F").Insert
    
    With Range("F2:F" & lngR)
        .FormulaR1C1 = "=VALUE(MID(RC[-1],6,3))>=7"
        .Value = .Value
    End With
    
    With Columns("C:F")
        .AutoFilter Field:=1, Criteria1:="Enrolled"
        .AutoFilter Field:=4, Criteria1:="TRUE"
    End With
    
    Range("C2:C" & lngR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Cells.AutoFilter
    Range("F:F").Delete
End Sub

**jimmisavage** · 06-12-2014, 10:57 AM

Thanks for the reply Bernie,
I actually wanted to apply both filters and then delete what was remaining so I don't think this would work (although i've not tested it). I've managed to record a macro now anyway, with a lot of cell manipulation - you could say the long way around but it does what i need it to do so i'm happy with that. I don't think it's worth me posting my result because i'm sure there is a much better way of doing it and it would take me an hour to type each step!

Thanks for your continues help everyone

**Bernie Deitrick** · 06-12-2014, 10:59 AM

apply both filters and then delete what was remaining

That is exactly what the code does.

**jimmisavage** · 06-12-2014, 11:01 AM

Ah, then that's much easier than my way

Thanks for your help anyway

Macro to filter 2 columns and then delete remaining rows

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to filter 2 columns and then delete remaining rows

Re: Macro to filter 2 columns and then delete remaining rows

Re: Macro to filter 2 columns and then delete remaining rows

Re: Macro to filter 2 columns and then delete remaining rows

Re: Macro to filter 2 columns and then delete remaining rows

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Want to Delete Specified Columns and then add Column header to the remaining ones

Delete & Merge Columns,Delete Rows with filter, etc

[SOLVED] Macro to delete sheets and saves remaining file does not properly delete module

[SOLVED] Re: Macro to delete sheets and saves remaining file does not properly delete module

[SOLVED] Re: Macro to delete sheets and saves remaining file does not properly delete module

Bookmarks

Bookmarks

Posting Permissions