Macro to filter 1 column and delete

[hanzsolo]

I'm trying to improve my macro to run a filter on Col Y and delete specific values. Col Y can be "DT", "IO", "PS", "RO", "TI", "TO", "WO", "ZZ" "(Blank)" but I only want to keep "IO", "PS", "RO".

I tried putting a space into each cell in the entire row where there is a value I want to delete, but the report changes daily and the number of orders is different every day.

Is there a way to do this through my macro? I can't put a filter on after the macro because I'm making a pivot table immediately after and it includes all data. Do I have to put a filter on after the macro runs, then put a filter on and delete all lines with the Col Y values I want to delete?

 

[Akinrotimi]

If I get you right,I want to delete the entirerow if the values in column Y is not IO,PR or RO.I assumed your data won't exceed 100,000 rows.

If so,you can run this macro below:

==========================================


Range("Y100011").Select
ActiveCell.FormulaR1C1 = "DT"
Range("Y100012").Select
ActiveCell.FormulaR1C1 = "TI"
Range("Y100013").Select
ActiveCell.FormulaR1C1 = "TO"
Range("Y100014").Select
ActiveCell.FormulaR1C1 = "WO"
Range("Y100015").Select
ActiveCell.FormulaR1C1 = "ZZ"
Columns("Y:Y").Select
Selection.Replace What:="DT", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="TI", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="TO", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="WO", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ZZ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Columns("Y:Y").Select
Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
Columns("y:y").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("A1").Select

end sub

==========================================

QED


Rotimi

 

[hanzsolo]

Thank you, this works great and did exactly what I requested. The report started at 19,928 lines, was reduced to 16,950 lines of data, and roughly 80,000 lines of (Blank).
The first time this caused my Excel to crash (running on a very old PC). Is there a way to apply this code:
**********
Range("Y100011").Select
ActiveCell.FormulaR1C1 = "DT"
Range("Y100012").Select
ActiveCell.FormulaR1C1 = "TI"
Range("Y100013").Select
ActiveCell.FormulaR1C1 = "TO"
Range("Y100014").Select
ActiveCell.FormulaR1C1 = "WO"
Range("Y100015").Select
ActiveCell.FormulaR1C1 = "ZZ"
Columns("Y:Y").Select
Selection.Replace What:="DT", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="TI", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="TO", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="WO", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="ZZ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Columns("Y:Y").Select
Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
Columns("y:y").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("A1").Select
**********

But add in :
==========
LR = Cells.Find(What:="*",SearchDirection:=xlPrevious,SearchOrder:=xlByRows).Row
Range("Y2").AutoFill Destination:=Range("Y2:Y" & LR)
==========
somewhere in the code. I'm not familiar with either of these codes so I don't know where to add it in, or if will even work.
I'm trying to find a way to eliminate all "(Blanks)", or apply the formula only to rows that have data in the cell.

 

[Akinrotimi]

What do you forsee to be the maximum number of rows you would ever have?

I thought you said you wanted to remove rows where column Y had those unwanted values?

If you want to reduce the limit for example to 25,000 rows.Find 1000 and relace with 250 to improve performance.

Let me kow if you still have any issues

Regards

Rotimi

 

[hanzsolo]

The maximum length would be 25,000 rows. I replaced it and ran it with 40,000 lines and it did run faster.

However, after I run my macro, I run a pivot table, and the "(blanks)" get counted into the "Grand Total." I'm trying to avoid adding those blanks to the grand total and giving me an inaccurate grand total.

I know how to get around this problem, however I'm interning and handing off this process to someone who hardly knows how to run a pivot table. I'm trying to make this as easy as possible for them, because if they get stuck, there is no one here that can help them.

 

[hanzsolo]

Which is why I want to keep everything in the macro, so all I have to tell them is to 'run the macro' and then 'run the pivot table' and follow the steps I gave them.