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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Using a specialized calendar control is a great way to let users add dates to a worksheet. Unfortuantely, Microsoft ...
Discover MoreWant to easily control which records get imported from a text file into Excel? It's easy to do when you write the macro ...
Discover MoreIf you need to find out how many columns are set to be a specific width, you'll need a macro to help determine the info. ...
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 © 2021 Sharon Parq Associates, Inc.
Comments