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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Need to know if a particular cell is clicked with the mouse? Excel has no particular event handler for clicking in this ...
Discover MoreCopying information using a macro is rather simple, although there are multiple ways you can do the copying. The most ...
Discover MoreWorkbooks can contain macros, or not. It is entirely up to you whether they do or not, but at some future time you might ...
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 © 2020 Sharon Parq Associates, Inc.
Comments