In MS Excel, I need to delete every alternate row AUTOMATICALLY rather than MANUALLY

Question

Ok! basically i'm working on an excel spreadsheet in which I have to delete every alternate row because it is messing up with my results.

Its a set of 4000 randomly generated set of data.

Hence, deleting 2000 wud take about 30 mins, can it be done in 30 seconds using EXCEL alone and avoiding Programming?

Is there any trick to do that in MS Excel?

PS: I want the deleted cell to be empty and not to be replaced with the following entry.

Thanks in advance!

Answer 1

You can use a macro to remove alternating rows, or you can cheat.

If it's sort of a one-off thing, I would cheat and add another column titled something like even. Then setup your data so it appears as the following:

value    item    even
10       foo     1
2        bar     2
11       baz     1
13       qux     2

You'd then filter based on the rows you wish to delete, select them and delete them. Filling out 1 and 2 is generally easy to just type the first four entries by hand, select the four values (1 2 1 2) and then double click the black box on the bottom right of the selection.

My hunch is that you might not even want to be doing that. In general whenever I've run across this particular need I've found a better way to solve it using Excel's built in tools, such as pivot tables.

Answer 2

A simple VBA script will do just fine. Save a Macro-Enabled copy of your Excel document. Create a macro, paste this code and click run.

Sub DeleteAlternateRow()
    For i = 1 To 4000
        Cells(i, 1).Select
        ActiveCell.EntireRow.Delete
    Next i
End Sub

Note: This macro deletes row 1,3,5,... if you want to start with deleting the second row then start the for loop with 2. Don't forget to make a copy of your document before running any macros

Answer 3

In a new column (say "X") use

=MOD(LINE(X1);2)

and expand the column over the whole set. That should give you an alternating sequence of 0 and 1. Now auto-filter and select only the 0s (or the 1s), then hit delete.

You may have to look up your local version of the functions, if you are not using english excel. For example in Germany it would be REST(ZEILE(X1;2)). MOD is the modulo function, LINE should give you the current line number.

Answer 4

use this macro http://support.microsoft.com/kb/213610