PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 4.00 average.

How do I delete alternating rows in excel

 
 
gump
Guest
Posts: n/a
 
      29th Jul 2009
I have over 5,000 rows of data. I only need the odd number rows (1,3,5) and
need to delete the even rows. Is there a faster way then hold CTRL and
clicking on even number rows and then deleting? How can select all the even
number of rows and then delete all?
 
Reply With Quote
 
 
 
 
Glenn
Guest
Posts: n/a
 
      29th Jul 2009
gump wrote:
> I have over 5,000 rows of data. I only need the odd number rows (1,3,5) and
> need to delete the even rows. Is there a faster way then hold CTRL and
> clicking on even number rows and then deleting? How can select all the even
> number of rows and then delete all?


Insert a column. Put =MOD(ROW(),2) in row 1 and copy down. Sort all of your
data by that column. Delete the appropriate rows. Delete the column.
 
Reply With Quote
 
 
 
 
Ken
Guest
Posts: n/a
 
      29th Jul 2009
gump

Put this formula in a spare column

=mod(row(),2)

copy it down all your relevant rows.

convert the formula to values (paste special values on top of the mod
formula)

sort by that column, the zeros will come to the top where they can all
be deleted at once

the other rows should still be in the same order as prior to the sort
since they are all 1's

Good luck

Ken
Norfolk, Va



On Jul 29, 3:41*pm, gump <(E-Mail Removed)> wrote:
> I have over 5,000 rows of data. *I only need the odd number rows (1,3,5) and
> need to delete the even rows. *Is there a faster way then hold CTRL and
> clicking on even number rows and then deleting? *How can select all theeven
> number of rows and then delete all?


 
Reply With Quote
 
Gord Dibben
Guest
Posts: n/a
 
      30th Jul 2009
You have a couple of suggestions but they both ibvolve sorting.

Do you want to do that?

If not, in an adjacent column in cell 2 enter an X

In cell 4 enter an X

Select cell 2 to cell 5

Double-click to fill down.

AutoFilter for X and delete those rows.


Gord Dibben MS Excel MVP

On Wed, 29 Jul 2009 12:41:02 -0700, gump <(E-Mail Removed)>
wrote:

>I have over 5,000 rows of data. I only need the odd number rows (1,3,5) and
>need to delete the even rows. Is there a faster way then hold CTRL and
>clicking on even number rows and then deleting? How can select all the even
>number of rows and then delete all?


 
Reply With Quote
 
New Member
Join Date: Mar 2012
Posts: 1
 
      13th Mar 2012
Quote:
Originally Posted by gump View Post
I have over 5,000 rows of data. I only need the odd number rows (1,3,5) and
need to delete the even rows. Is there a faster way then hold CTRL and
clicking on even number rows and then deleting? How can select all the even
number of rows and then delete all?
Insert a column. Put =MOD(ROW(),2) in row 1 and copy down.
Alternate rows will be filled with zeros and ones.
Set filter to zero or one as required
Only alternate rows will be visible
press F5 and select visible cells
press Ctrl+C to copy
and Ctrl +V to paste to a new sheet.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to delete alternating BLANK rows in .xls file all @ once? Desperate Microsoft Excel Misc 3 27th Oct 2009 09:34 PM
Alternating gray rows on my Excel reports ScottSisk Microsoft Excel Discussion 10 27th Mar 2006 04:41 PM
shade alternating rows in excel =?Utf-8?B?TWF0dDgzMDE=?= Microsoft Excel Misc 2 28th Jul 2005 08:54 PM
Excel sorting - data on alternating rows =?Utf-8?B?SmFjayBS?= Windows XP Accessibility 1 30th Mar 2005 03:54 AM
Shading alternating rows for easy visibilty - won't stay alternating when filtering djmiller777 Microsoft Excel Misc 2 9th Jan 2004 10:48 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:37 AM.