How do I delete rows based upon the content (without sorting the .

[Guest]

After importing an ASCII print file, I'm now trying to get rid of the headers
and footers. Any suggestions on how to do this in a automated fashion?

 

[Anne Troy]

Try something like this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=20

**** Hope it helps! ****

~Dreamboat
Excel VBA Certification Coming Soon!
www.VBAExpress.com/training/
********************************

 

[Evan]

Sorting might bunch up the target lines that would be easy
to delete. But first set up an index column alongside so
that you can regain the original order with another sort.

Evan Weiner
Richland WA

 

[Lady Layla]

That doesnt look like it will work Can you elaborate more as to how to make it
useful in this example?


: Try something like this:
: http://www.vbaexpress.com/kb/getarticle.php?kb_id=20
:
: **** Hope it helps! ****
:
: ~Dreamboat
: Excel VBA Certification Coming Soon!
: www.VBAExpress.com/training/
: ********************************
: : > After importing an ASCII print file, I'm now trying to get rid of the
: headers
: > and footers. Any suggestions on how to do this in a automated fashion?
: >
:
:

 

[Frank Kabel]

Hi
provide some more details HOW to identify footers/headers in your data

 

[Dave Peterson]

You could record a macro when you import the file.
Apply data|filter|autofilter to the column that contains the headers/footers
Show each header row
delete the visible rows in the filtered range
show each footer
delete the visible rows in the filtered range

unfilter the worksheet.

(It doesn't take too much time even if you do it manually.)

 

[Guest]

Anne,

Thanks! I ended up using a small program that works about the same as the
one you pointed me to. I saved the file in a different name and wrote a VBA
routine to:

loop up from the bottom to the top
match a cell to a string (in this case, I was in column A so it was
CELL(row,1))
If it matched, I deleted it.
do the next one

Chuck

 

[Guest]

Another excellent idea! I had not thought of numbering all the rows, sorting
to delete the headers and then resorting by the numbers.

That would have saved me some time!

Chuck

 

[Guest]

Ok, that's three good answers!

Dave,

While I did finally solve my problem by writing a bit of code, I think your
answer would have been the fastest to implement. Almost as fast would have
been Evan's sequential Number/sort/resort idea.

In my case, the report format for the headers and footers was quite regular
and it would have been easy to filter or sort - plus I would get to see just
what I was going to delete before hand.

Thanks!