Excel Macro 2

[bac2u]

I need to remove the banner rows on a print file I imported into Excel. Every 63 rows, there are 9 rows of banner stuff I need to get rid of. The spreadsheet is about 8,000 rows big. I know I should be able to use a macro but am not familiar enough to do it. I can't sort the spreadsheet to get rid of the banner rows because the rest of the data is in paired rows that have no similar fields between them.

any help greatly appreciated.

Barb

 

[SkipVought]

Hi,

Here's a process to accomplish that.

1) Number each row of data sequentially

2) sort the data (not the sequential numbers)

3) locate the rows containing the repeating heading data and DELETE the rows (don't leave empty rows)

4) sort by the sequential numbers -- puts it back into the original sequence

VOLA

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

Give us some more info about your data and perhaps an example of normal data + banner data. There are numerous ways of tidying up stuff like this but we need to know aht we are dealing with.

Is all the data in a single column, or does it get parsed across various columns when you import? if so then is there a single column that you could reliably say that if the cell in this column is blank, then the row should be deleted etc

The more info you give us the easier it is to help.

Is it simply as you say, every 63 rows, lose 9 etc. If so then is the banner info in the last 9 rows of the file, or does it end with the 63 rows of data.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[bac2u]

Hi Skip and Ken -- sorry for delay in responding, I was out for a while...

Anyway, Skip's way will work, great suggestion!!

But I'm sure there's a way to do this in a macro too. Here's a sample of the data: I need to get rid of those banner rows whenever a page break occurs. It's actually 9 rows, but the line wraps here so it looks like more. After the banner rows you can see the rest of the data - clumped together in two rows of info tied to the event number in Col. A. You can see that after event #248493, nine rows separate that line from the line that goes with that. Thanks so much for any more ideas!

E97COU ILLIAR 31015 EDMONDS SCHOOL
03/15/ 04 10:45:13 AM MONTHLY AUDIT

====== ============================= =========== ==================
Typ e: Certificated
Date(s ): 02/11/2004-03/11/2004
LV
Event Employee/Substitute CD Dates(s)
------ ---------------------------- -------- -- -----------------


Locati on: 001 BEVERLY ELEMENTARY
248130 ARNOLD, ANNE J 30473 PC 02/23/04-02/23/04
HOGUE, KAREN R 29361 02/23/04-02/23/04



247205 ARNOLD, ANNE J 30473 S 02/25/04-02/25/04
HOGUE, KAREN R 29361 02/25/04-02/25/04



248019 BAUMGARTNER, ROBERT J 26114 M 03/05/04-03/05/04
SONG, EUI J 30665 03/05/04-03/05/04



247298 BAUMGARTNER, ROBERT J 26114 M 02/12/04-02/12/04
SONG, EUI J 30665 02/12/04-02/12/04



247299 BAUMGARTNER, ROBERT J 26114 M 02/12/04-02/12/04
CANNON, CAROLE T 16311 02/12/04-02/12/04



247201 BAUMGARTNER, ROBERT J 26114 M 02/25/04-02/25/04
SONG, EUI J 30665 02/25/04-02/25/04



247202 BAUMGARTNER, ROBERT J 26114 M 02/25/04-02/25/04
MANZER, SHAWN D 29110 02/25/04-02/25/04



247852 BRUNSKILL, CLARE 21345 S 02/12/04-02/12/04
SWAIN, CYNTHIA A 26994 02/12/04-02/12/04



247857 CAESAR, CHERYL L 25555 M 03/03/04-03/03/04
HEDGES, SARA M 32472 03/03/04-03/03/04



246970 DE SMET, ANN 18722 S 03/02/04-03/02/04
248536 FLOATING SUB 99992 M 03/10/04-03/10/04
HEDGES, SARA M 32472 03/10/04-03/10/04



248493 FLOATING SUB 99992 M 03/10/04-03/10/04
E97CO UILLIAR 31015 EDMONDS SCHOO
03/15/ 04 10:45:13 AM MONTHLY AUDIT

====== ============================= =========== ==================
Typ e: Certificated
Date(s ): 02/11/2004-03/11/2004
LV
Event Employee/Substitute CD Dates(s)
------ ---------------------------- -------- -- -----------------
BORCHERT, DIANNE C 17990 03/10/04-03/10/04



248516 FLOATING SUB 99992 M 03/10/04-03/10/04
MAKSIM, BARBARA A 25771 03/10/04-03/10/04



248482 HENDERSON, LAURA A 29700 S 03/01/04-03/01/04
KOREN, SOFIA 30492 03/01/04-03/01/04



248483 HENDERSON, LAURA A 29700 M 03/04/04-03/04/04
BARRY, SUZANNE M 22364 03/04/04-03/04/04



247624 LARGENT, JENIFER M 24593 S 03/10/04-03/10/04
SWAIN, CYNTHIA A 26994 03/10/04-03/10/04



248104 LARGENT, JENIFER M 24593 M 03/03/04-03/03/04
HOGUE, KAREN R 29361 03/03/04-03/03/04



248046 LORETTE, SALLY B 4808 S 02/25/04-02/25/04
SWAIN, CYNTHIA A 26994 02/25/04-02/25/04



247422 MAGER, INGRID N 15715 M 03/04/04-03/04/04
OTTO, ROGER E 32391 03/04/04-03/04/04



247421 MAGER, INGRID N 15715 M 03/03/04-03/03/04
MAKSIM, BARBARA A 25771 03/03/04-03/03/04



248138 REAY, VIANNE K 28025 S 03/09/04-03/09/04
YUKIMURA, HINDA L 25055 03/09/04-03/09/04



248137 REAY, VIANNE K 28025 M 02/13/04-02/13/04
HUDSON, LISA A 29372 02/13/04-02/13/04



247406 RICHTER, SARAH E 32121 PC 02/13/04-02/13/04
E97CO UILLIAR 31015 EDMONDS SCHOO
03/15/ 04 10:45:13 AM MONTHLY AUDIT

====== ============================= =========== ==================
Typ e: Certificated
Date(s ): 02/11/2004-03/11/2004
LV
Event Employee/Substitute CD Dates(s)
------ ---------------------------- -------- -- -----------------
BORCHERT, DIANNE C 17990 02/13/04-02/13/04



247297 ROARK, JULIE A 26113 M 03/04/04-03/04/04
KARREN, KACEY L 32163 03/04/04-03/04/04

 

[SkipVought]

Sure, its possible. But you posted in the MS Office Forum as opposed to the MS VBA Forum, so a SPREADSHEET solution was suggested.

Would you like a VBA solution, too?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bac2u]

Yes, I suppose you're right. I always forget that macros in Excel are never Excel, they're VB. I hate that! I'm old enough to remember Lotus and how easy it was to write macros, even looping macros, without being a programmer.

Anyway, thanks again for your suggestion. It should work.

Barb

 

[KenWright]

One of the places I was going, was that if by chance you have a column that you know will contain data in all the rows you want to keep, but will be blank in the rows you want to lose, you can select that column, do Edit / Go To / Special / Blanks, then do Edit / Delete / Entire Row and they will all disappear.

I couldn't work out if your data is all in Col A, or whether it is spread across columns, in which case this may well be an option for you.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------