Tuesday, 7 July 2009

How to delete every other row in excel

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 :


[sourcecode]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[/sourcecode]

  • 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