Deleting several rows every nth row 
Author Message
 Deleting several rows every nth row

I have imported a report that summarizes information on vendors at the end
of each vendors data.  Unfortunately, there is no identification of which
vendor that data belongs to in each row so sorting the data by the vendor
name or number is not an option.  To complicate the issue, I have page
breaks that the standard report has put in the file since I imported a tab
delimitated report.  This header information appears every nth row and the
total report is some 8000 plus rows long.  If anyone has some idea of how to
select every nth row and then delete it plus several after it I would be
greatly appreciative.  I have some year end reports that I am running late
on getting to the boss.

Thanks,

Lantz



Sun, 05 May 2002 03:00:00 GMT  
 Deleting several rows every nth row
Hi Lantz,
If it is really necessary to delete every nth line.  You should loop through
your rows by going up instead of down  (step -1).  But I expect that
you can use something else to identify the lines to be removed.  You can
probably find something at Chip Peason's site along the lines of
    http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows
you will have to change the criteria.  But you would be using something
like the line which would delete row R within the range.
    Rng.Rows(R).EntireRow.Delete

HTH,
David McRitchie,  Microsoft MVP - Excel
My Excel Pages:  http://members.aol.com/dmcritchie/excel/excel.htm

Quote:

> I have imported a report that summarizes information on vendors at the end
> of each vendors data.  Unfortunately, there is no identification of which
> vendor that data belongs to in each row so sorting the data by the vendor
> name or number is not an option.  To complicate the issue, I have page
> breaks that the standard report has put in the file since I imported a tab
> delimitated report.  This header information appears every nth row and the
> total report is some 8000 plus rows long.  If anyone has some idea of how to
> select every nth row and then delete it plus several after it I would be
> greatly appreciative.  I have some year end reports that I am running late
> on getting to the boss.

> Thanks,

> Lantz



Sun, 05 May 2002 03:00:00 GMT  
 Deleting several rows every nth row
Lantz,

Since you have 8000 rows, I suggest you insert a column and put a formula that
will return 1 for the header row or extra row and 0 for the good rows. For
example, if you insert a blank column A, the formula in A2:
=IF(OR(B2="Header",B2=""),1,0)
will return 1 or 0 depending on the contents of B2.  Copy this down to match
your data, then sort on column A, and select and delete all the rows that have a
1 in column A.  (This will be much faster than trying to delete the individual
rows one by one, even if you are using a macro.) And then you're done.  (You may
want to insert another column with a number series in it to be able to restore
your order after the deletion in case it gets screwed up, but usually the order
is maintained.)

HTH,
Bernie

Quote:

> I have imported a report that summarizes information on vendors at the end
> of each vendors data.  Unfortunately, there is no identification of which
> vendor that data belongs to in each row so sorting the data by the vendor
> name or number is not an option.  To complicate the issue, I have page
> breaks that the standard report has put in the file since I imported a tab
> delimitated report.  This header information appears every nth row and the
> total report is some 8000 plus rows long.  If anyone has some idea of how to
> select every nth row and then delete it plus several after it I would be
> greatly appreciative.  I have some year end reports that I am running late
> on getting to the boss.

> Thanks,

> Lantz



Sun, 05 May 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Copy every Nth Cell in a Row?

2. Select Every Nth Row??

3. How to pick every Nth row???

4. Sum every Nth row... with conditions

5. filtering every nth row???

6. Getting #deleted message in every field of every row when linking to a SQL Server DB

7. hiding rows and next several rows

8. Delete row does not delete all of row

9. Delete Row does not delete the row's cells in hidden columns with Autofilter filtering

10. Concatenating a field in several rows into one row


 
Powered by phpBB® Forum Software © phpBB Group