Automating Processes - Filter & Delete Rows

[RichCowell]

Hi all,
I regularly download CSVs of delegate info for events.
However, they include a lot more rows than I need.
To get around that I usually convert it to a table as I often need that later, then hide the rows containing "Attending" in "Column1", then delete the remaining rows, then show the Attending again.

Can anyone suggest a way of automating this to save me faffing around repeating it every time?


e.g.

	Forename	Surname	Email	Workplace	Column1	Column2
1	Joe	Bloggs	joe.bloggs@bloggs.com	Bloggs inc	No Response	None
2	Charles	Windsor	hrh@royal.co.uk	The Palace	Attending	None
3	Sid	James	sid@carryon.uk	Rank Organisation	Not Attending	None
4	Taylor	Swift	tswift@taylorswift.com	NA	Not Attending	None
5	Winston	Churchill	winston@churchill.co.uk	UK Gov	Attending	None

So I need to delete all the rows with "No Response" or "Not Attending" in the Column1 column, leaving only the rows with "Attending"

This is done on multiple CSVs so I'd like to avoid creating a template to copy and paste the data into if possible.

Thanks!
Rick

 

[MARK858]

Is this a range or a table when you are going to run the Autofilter? if it is a table what is the Tables name?

 

[RichCowell]

It is just a column in a table; I don't name it, so it is just Table1, and the 8th column in the table.

 

[MARK858]

Try...

    With ActiveSheet.ListObjects("Table1").DataBodyRange
        .AutoFilter 8, "<>Attending", xlAnd
        .EntireRow.Delete
        .AutoFilter
    End With

 

[RichCowell]

I'll get that tested asap, thank you!