Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Deleting Every X Rows.
When you import data from an outside source, you may run into a need to delete extraneous data from a worksheet. For instance, you may have a need to remove every second line from the data, or every fifth line. Doing this by hand can be tedious and prone to error. Fortunately, you can create a macro to help eliminate both the tedium and the errors.
The following macro, DeleteRows, will remove every X rows from your worksheet. All you have to do is select the rows you want it applied to. The macro, as written, will remove every second row. So, if you wanted to delete the first, third, fifth, and seventh rows beginning with row 10, you would select rows 10 through 16 and then run this macro. It results in rows 10 (the first row), 12 (the third row), 14 (the fifth row), and 16 (the seventh row) being deleted.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Sub DeleteRows()
Dim iStart As Integer
Dim iEnd As Integer
Dim iCount As Integer
Dim iStep As Integer
Dim J As Integer
iStep = 2 'Delete every 2nd row
Application.ScreenUpdating = False
iStart = 1
iCount = Selection.Rows.Count
'Find ending row to start deleting
For J = iStart To iCount Step iStep
iEnd = J
Next
Do While iEnd >= iStart
Selection.Rows(iEnd).Delete
iEnd = iEnd — iStep
Loop
Application.ScreenUpdating = True
End Sub
If you want to delete some other multiple of lines, simply change the setting for the iStep variable. For instance, if you want to delete every fifth row, change iStep from 2 to 5. (You only need to make the single change, in the iStep = 2 declaration.)
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3592) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Deleting Every X Rows.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Macros depend on the use of variables to do their work. This tip examines how variables are declared in a macro, using ...
Discover MoreWhen writing a macro, you may want to fill a range of cells with different values. The easiest way to do this is to use ...
Discover MoreYou can use macros to process information in your worksheets. You may want to use that macro to apply the italic ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-05-21 11:38:59
Robert Cline
By deleting whatever character it was that was copied and pasted in to your Macro, replacing the character with a minus sign, as you suggested, worked like a charm.
Thank you Allen for a useful Macro.
Robert
2018-05-19 10:21:49
Allen
Robert: I suspect that if you copied and pasted the code into the Visual Basic Editor, the minus sign is not really a minus sign. Delete it and type a minus sign, and it should work fine.
-Allen
2018-05-19 07:31:49
Robert Cline
Thanks for the code Allen. I am using Excel 2016.
I get a syntax error at line: iEnd = iEnd – iStep
Am I missing something here?
Thank you.
Robert Cline
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Comments