If you want to delete every other row in Excel, you’ll need to use the following macro.
- On the Tools menu, point to Macro, and then click Visual Basic Editor.
- For Excel 2007, click Visual Basic in the Code group on the Developer tab.
- On the Insert menu, click Module.
- Then copy paste the following code :
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
- Switch to the worksheet that contains the data, and then select the cell that you want to delete.
- To run the macro, point to Macro on the Tools menu, and then click Macros. (For Excel 2007, click Macros in the Code group on the Developer tab.)
- Select the Delete_Every_Other_Row macro, and then click Run.
Hopefully, future Excel versions will have built-in functions to delete every other row.
Source : Microsoft KB