How do I delete alternating rows in excel

G

gump

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?
 
G

Glenn

gump said:
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.
 
K

Ken

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
 
G

Gord Dibben

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
 
Joined
Mar 13, 2012
Messages
1
Reaction score
0
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top