Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Deleting Every X Rows.

Deleting Every X Rows

by Allen Wyatt
(last updated November 6, 2015)

8

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.

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.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2292) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Deleting Every X Rows.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Quickly Displaying Formatting Specs

It's easy to apply formatting to text, but often hard (after the fact) to know exactly what was done. If you often need to ...

Discover More

Word Find and Replace (Table of Contents)

The Find and Replace tool is one of the most-used tools provided by Word. However, few people know how to effectively, ...

Discover More

Reducing the Curl in Printed Documents

Have you ever printed out a document, only to have the pages curl very badly as they come out of the printer? There's a ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (menu)

Adding Leading Zeroes to ZIP Codes

Import a bunch of ZIP Codes into Excel, and you may be surprised that any leading zeroes disappear. Here's a handy little ...

Discover More

Relative VBA Selections

Need to select a cell using a macro? Need that selection to be relative to the cell you currently have selected? Here's the ...

Discover More

Making a Cell's Contents Italics within a Macro

You can use macros to process information in your worksheets. You may want to use that macro to apply the italic attribute to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 - 2?

2015-09-17 04:56:07

Chris JG

This works well for small selections but for massive ones (nearly 50,000 rows) I had two problems.

The first is that integer can only go up to 32,767 so i had to change this to Long.

Second is that one a massive selection like mine it takes forever. I'm looking to optimise this. I'll post the results if I get something working.


2013-04-11 05:50:44

Dave Kerr

Hi Andrew,

You're very welcome. I'm glad to have been of help and I really enjoyed the challenge!

All the best,
Dave


2013-04-11 03:27:40

Andrew McQuillen

Hi Dave,

This is absolutely spot on.
I have to add around 8 lines of data a day and then move them all down by 12 lines each. very tedious.
So now one press sorts it all out.
I really appreciate the time you have taken with this.
Many thanks

Andrew


2013-04-05 07:34:50

Dave K

Hi Andrew,

In theory, anything is possible! Actually so is your request. I have amended my code to accommodate your change. This time it inserts lines after every selected row, which I think is more aligned with what you are looking for.

You mentioned inserting 12 lines, but actually only 11 are inserted as the existing line becomes #1. I assume that this is what you intended.

I have hard-coded the number of lines to insert (8) by assigning this value to the variable "j". You will see that I have "commented out" the next line, which prompts the user for how many lines to insert. Note, though, that I have not done any validation e.g. to make sure that the input is numeric. So, you have some flexibility built in.

Anyway, here's the code. I hope you find it useful (and that it is legible on this screen!).

Cheers,
Dave.


Sub Andrew()
' macro to insert numbered rows into a selected block

Dim iStart As Integer
Dim RowKnt As Integer
Dim iEnd As Integer
Dim i As Integer
Dim j As Integer
Dim InsKnt As Integer

' number of rows to insert
j = 8
' j = InputBox("How many rows to Insert?")
iStart = Selection.Row()
' first row selected
RowKnt = Selection.Rows.Count
' number of rows selected

i = iStart
iEnd = iStart + RowKnt

While i < iEnd
For InsKnt = 1 To j - 1
Range("A" & i).Select
Range("D" & i).Value = InsKnt
i = i + 1
Range("D" & i).EntireRow.Insert
Range("D" & i).Value = InsKnt + 1
iEnd = iEnd + 1
Next InsKnt
i = i + 1
Wend
End Sub


2013-04-04 08:11:57

Andrew McQuillen

Hi Dave ,
I just checked what what has been posted. All the numbers should be under one another in column D it doesn't look like this in my example.

Thanks again

Andrew


2013-04-04 08:09:23

Andrew McQuillen

Hi Dave,
That works really well. I think that i didn't explain the problem exactly. I would like the first line to contain the 1 in the 4th column, down to 12 then the next line to be 1 again eg

part Number A 1
2
3
4
5
6
7
8
9
10
11
12
part Number B 1
2
3
4
etc
This possibly explains it better.
I don't know if this is even possible.

Thanks for any help.

Andrew


2013-04-03 05:56:01

Dave K

Hi Andrew,

I have written a macro which will insert rows for you, and insert numbers into column D in the inserted rows. I trust that this is what you want. It may not be the most elegant solution, but it works! By the way, I use Excel 97 but I expect that the code will work in later versions too. Here is the code...

Dim iStart As Integer
Dim RowKnt As Integer
Dim j As Integer
Dim InsKnt As Integer

Application.ScreenUpdating = False

j = 12 ' number of rows to insert
iStart = Selection.Row() ' first row selected
RowKnt = Selection.Rows.Count ' number of rows selected

While RowKnt > 1
For InsKnt = 1 To j
Selection.Rows(RowKnt).EntireRow.Insert
Range("D" & (ActiveCell.Row + RowKnt - 1)).Value = j - InsKnt + 1
Next InsKnt
RowKnt = RowKnt - 1
Wend
ActiveCell.Select
Application.ScreenUpdating = True
End Sub

When you want to run the macro, select the rows where you want insertions to take place. Rows are not inserted past the last cell selected.

Regards,
Dave


2013-04-02 08:31:06

Andrew McQuillen

I think you need to move the NEXT command to the end or in fact just above the End Sub

ie
Do While iEnd >= iStart
Selection.Rows(iEnd).Delete
iEnd = iEnd–iStep
Loop
Application.ScreenUpdating = True
Next
End Sub

It didn't work for me before and now it does.

I also included the line:
iEnd = iEnd–iStep
as this caused an error when i first loaded it in and i rewrote it out and it formated itself correctly. may be just my copying and pasting but this also works now.

I would really like to have a macro similar to this but inserting 12 lines between each entry then adding into the 4th column the numbers 1 to 12.

Thanks
Andrew


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.