Browse » Home
Monday, December 17, 2012
How to Delete Every Other Row of an Excel Spreadsheet
1. Open the Excel Visual Basic Editor. With your spreadsheet open in Excel, click 'Tools' from the menu near the top of your screen, then select 'Macro' and 'Visual Basic Editor.' If you use Excel 2003 or older, go on to the next step.Excel 2007 users will notice that the previous command does not exist in the current version of Excel, and that no macro editing tools appear by default. Turn on access to these tools by clicking the Office button at the top-left corner of your screen, then clicking the 'Excel Options' button at the bottom of the menu. Locate the heading labeled 'Top options for working with Excel' in the resulting dialog box, then check the box next to the 'Show Developer tab in the Ribbon' label. Click 'OK.' Select the Developer tab, then click the 'Visual Basic' button.
2. Add macro code. Click 'Insert' from the menu near the top of the Microsoft Visual Basic editor window and choose 'Module.' Copy all of the code listed below, then paste it into this module: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
3. Run the macro. Return to your spreadsheet without closing the Visual Basic editor. Highlight the rows for which alternating row should be removed. In Excel 2003 and earlier, click the 'Tools' menu option, then select 'Macro', followed by the 'Macros' option. In Excel 2007, select the Developer tab, then click 'Macros.' To run the macro in either version, select the macro labeled 'Delete_Every_Other_Row', then click 'Run.' Every even-numbered row of your spreadsheet deletes.