Delete Rows Based on Conditions

[AbiG2009]

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

[Admin]

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.


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

[AbiG2009]

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

[AbiG2009]

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

[Admin]

Hi

Thanks for the feedback.

[Norman]

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

[DocAElstein]

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/showthread.php/2293-Move-values-in-rows-at-the-end-of-the-preceding-row?p=10851#post10851 )
I will take a look at that Thread shortly and then post there

Alan