Delete Rows Based on Conditions

Please forgive me if this is answered elsewhere-I've searched, but apparently don't know correct terms to search:

I have data that I can block, copy, and then Paste into Excel 2007. (This is the only way to obtain this data). This is basically unformatted data that pastes multiple rows (~1500 rows) into Column A.

Since I need to do this every week, is there a way I can Paste it into a worksheet/workbook that automatically/semi-automatically:
1--Deletes all rows that begin with certain terms like: "Fill List" OR "Printed" OR "DOB:" OR "(et" OR "Aller"
AND
2--Deletes all rows that contain "Fill cycle"

Once it does this, I think I can separate the strings into different cells by formulas that I can then send to a Pivot Table.

I am a rookie at VBA -- I know how to open Visual Basic, but that's about all. If you could answer or direct me to a thread that does would be would be greatly appreciated.

Thanks,
AbiG

Hi AbiG,

Welcome to ExcelFox!!

Try this code.

Hit Alt+F11 > goto Insert > Module and paste this code there on the white pane. Alt+Q to close VBE and run kTest.

Code:

---

Option Explicit

Sub kTest()
   
    Dim r      As Range
    Dim c      As Range
    Dim i      As Long
    Dim x, Flg  As Boolean, Skip As Boolean
   
    Const SearchKeysBeginsWith      As String = "Fill List,Printed,DOB:,(et,Aller" '<< add more words separated by comma
    Const SearchKeysContains        As String = "Fill Cycle" '<< add more words separated by comma
   
    Set r = Range("a1:a1500")  '<< adjust to suit
   
    Application.ScreenUpdating = 0
   
    With r
        x = Split(SearchKeysBeginsWith, ",")
1:
        For i = 0 To UBound(x)
            .AutoFilter 1, IIf(Flg, "*" & x(i) & "*", x(i) & "*")
            On Error Resume Next
            Set c = .Cells(1).Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(12)
            On Error GoTo 0
            If Not c Is Nothing Then
                c.EntireRow.Delete
            End If
        Next
        If Not Skip Then
            x = Split(SearchKeysContains, ",")
            Flg = True: Skip = True: GoTo 1
        End If
        .AutoFilter
    End With
   
    Application.ScreenUpdating = 1
   
End Sub

---

I tried it and it seemed to work good, except I noticed A1 started with "Printed" and was still there. I also noticed a couple of other things, so I added them:

onst SearchKeysBeginsWith As String = "Fill List,Printed,DOB:,(et,Aller,Generic,Rx #" '<< add more words separated by comma

When I ran it again, it stopped. The Spreadsheet was Filtered, with only Row 1 showing, and the rest empty. I ran Debugger which highlighted this line: c.EntireRow.Delete

Maybe I shouldn't have run it twice?

Any ideas?
Thanks
AbiG

OK--I pasted it into another worksheet and ran again. It did fine. Maybe it was running it twice that did it. So, I won't run it twice.

In any case--it works great! Thanks a lot!
AbiG %D

Hi

Thanks for the feedback.

Dear Admin

Thanks for the great code.
I would like to know how to get the code to start from row A1 but also to remove rows that contain the dates expression with following symbol "/" such as 9/12/2018.
Finally, how to move the numbers in rows <>"2018" at the end of the previous row. Any idea?

Many thanks for the help and sharing knowledge

Cheers
Norman

Hello Norman,
Welcome to excelfox
I am not too familiar with the code from Admin here, but I think adding a search key ( SearchKeysContains ) of a form something like this might be the sort of thing you would need
"*" & "/" "*" & "/" & "*"
The idea behind that is that many things in VBA code which take strings in them will read the * as meaning anything.
So if a code looks for, pseudo like ...._
Anything & "/" Anything & "/" & Anything
_.... then a date of like 9/12/2018 would satisfy that.

As I am not too familiar with the code in this Thread
It might be easier for me to start a fresh in the other Thread that you have posted, ( http://www.excelfox.com/forum/showth...0851#post10851 )
I will take a look at that Thread shortly and then post there

Alan