A way to remove several reoccuring lines from an import?

E

Eldraad

due to the formatting of a report that I import as a text document int
excel I get a problem with page breaks and other information that i
not needed.

Here is an example of one of the routines I am using to get rid of th
unwanted rows (well, it is duplicated every page break so there ar
many of them)...

(Someone else posted this macro and I used it for this...)

Sub LABR18000()

Dim C As Range

On Error GoTo 1

2: Set C = [a2:a1900].Find(" LABR81000", MatchCase:=False
lookat:=xlWhole, LookIn:=xlValues)

C.EntireRow.Delete

GoTo 2

1: End Sub

In the above case I was removing all rows that begin with "LABR81000".
Most of the rows that get deleted start in the A column except one.

Now, there are about 5 different lines that I need to remove and eac
one appears many times. What would be the best way to incorporate al
5 macros into one macro?

I would be thankful for any help on this...

Thanks for your time in reading this question...

Eldraa
 
D

Dave Peterson

How about this:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myFindStrings As Variant
Dim FoundCell As Range
Dim iCtr As Long

myFindStrings = Array("asdf1", "asdf2", "asdf3")

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
With myRng
For iCtr = LBound(myFindStrings) To UBound(myFindStrings)
Do
Set FoundCell = .Cells.Find(what:=myFindStrings(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False)
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
Next iCtr
End With
End With
End Sub


Another way (manual) is to apply data|filter|autofilter to that column. Filter
for each value you want, and delete those visible rows.





Eldraad < said:
due to the formatting of a report that I import as a text document into
excel I get a problem with page breaks and other information that is
not needed.

Here is an example of one of the routines I am using to get rid of the
unwanted rows (well, it is duplicated every page break so there are
many of them)...

(Someone else posted this macro and I used it for this...)

Sub LABR18000()

Dim C As Range

On Error GoTo 1

2: Set C = [a2:a1900].Find("LABR81000", MatchCase:=False,
lookat:=xlWhole, LookIn:=xlValues)

C.EntireRow.Delete

GoTo 2

1: End Sub

In the above case I was removing all rows that begin with "LABR81000".
Most of the rows that get deleted start in the A column except one.

Now, there are about 5 different lines that I need to remove and each
one appears many times. What would be the best way to incorporate all
5 macros into one macro?

I would be thankful for any help on this...

Thanks for your time in reading this question...

Eldraad
Click to expand...
 
E

Eldraad

Dave Peterson, thank you.

It was a good attempt, but it looks like it was set up to look in the
column only. I gave the array the text I was looking for and gave th
"sheet1" the proper name. When it ran, it didn't get the stuff tha
appears in column J or further out. And a bit of a problems wit
getting rid of this text..."********" which they ised to divide som
sections. In the example I gave it works but in this program it seem
to cause a looping problem that wipes out most of the report LOL!

The visual effect was the same however (worksheet stuttering) and I ha
to put it into use this morning. I created a sub routine that jus
listed the manes of all the line deleting macros that need to run an
put them in the worksheet to run at startup AFTER the report wa
imported.

Yours is MUCH cleaner and takes up less space so I will try modifyin
some more to reach those areas in the center of the report.

Thanks again
 
D

Dave Peterson

to look for a single asterisk, look for ~*.

So to find 5 of them, look for ~*~*~*~*~*
 
Top