Auto Filter and Auto Format

[ryannjohnsonn]

Hello! I have another quick question!

I have a large sheet imported from another program that varies in length from 30,000-80,000 rows. I'm making a macro that will completely format it for me in a way that is understandable rather than spending an extended amount of time each month setting it up.

I want to include in my macro the ability to delete any row where Column O isn't equal to "CHECK", Column I is less than 69, Column F is less than 1, and Column H is not equal to CKOT.

I've played around with this, and anything I do goes line by line, which as you can imagine would take forever with 80,000 rows. I assume theres a way to filter it with VBA to quickly purge the unnecessary data?

After that data is deleted, I want to sort the data alphabetically based on Column C

Thanks for the help!

[Artik_PL]

1. Create Filter on column O, with criterion "CHECK".
2. Select and delete the filtered rows.
3. Sort ascending column C.

Do you need a macro for that? If so, then record them.

Artik

[ryannjohnsonn]

Yes, I'd like to have this as a macro to save me time in the long run.

I've recorded it and played around with it, but it's inefficient and doesn't account for the fact that the number of rows drastically changes from period to period.

[Artik_PL]

For example:
Line of code like:

ActiveSheet.Range("$A$1:$O$80000").AutoFilter Field:=15, Criteria1:="CHECK"

change on:

ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=15, Criteria1:="CHECK"

Other lines like:

Rows("3:80000").Select
Selection.Delete Shift:=xlUp

change on:

ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

Artik

[ryannjohnsonn]

I've played arond with it a bit more to no avail. I'm looking for it to verify the range of data using something like:

Range("K2").Resize(Cells(Rows.Count, "D").End(xlUp).Offset(-1).Row).Select

I don't want to set it to 80,000 or any other finite number because I can't consistently say how big the file would be.

Can anyone else help me with building this please?

[arlu1201]

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.