Bridge to Nova

Have you ever looking for option to delete every odd or even rows in Excel? Looking through menu after menu, hoping there’s some option hidden somewhere?

Well, the bad news is that there’s no built-in function as of Excel 2007. You need to use some marco to do just that.

Here’s the procedure. This procedure applies to all Excel versions from 97 to 2007, however I’ll provide screenshot using Excel 2007.

• Open your Excel file.

• In Excel 2007, Go to Developer tab, click Visual Basic. (All other Excel versions, go to Tools menu -> Marco –> Visual Basic Editor)

• Then in Visual Basic window, Insert Menu –> Module

• After that copy and paste the following code into the module window.

Sub Delete_Every_Other_Row()

   ' Dimension variables.
   Y = False              ' Change this to True if you want to
                          ' delete rows 1, 3, 5, and so on.
   I = 1
   Set xRng = Selection

   ' Loop once for every row in the selection.
   For xCounter = 1 To xRng.Rows.Count

       ' If Y is True, then...
       If Y = True Then

           ' ...delete an entire row of cells.
           xRng.Cells(I).EntireRow.Delete

       ' Otherwise...
       Else

           ' ...increment I by one so we can cycle through range.
           I = I + 1

       End If

       ' If Y is True, make it False; if Y is False, make it True.
       Y = Not Y

   Next xCounter

End Sub

• Go back to your excel workbook,  Select the Cells that you want for alternative rows to delete. Note : It is important to select the cells, if you select the whole column, Excel will most likely hang when you run Macro.

• After that, click Marcos under Developer tab in Excel 2007. (All other excel versions, Tools menu –> Marcos ) You should see the dialog box as follow. Click on Run.

Bravo! That’s it! Hopefully Excel 2010 will have built-in function for this. 

Source : Microsoft