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

Removing Pictures for a Worksheet in VBA

Excel allows you to add pictures to your worksheet, even within a macro. However, you might have a bit harder time figuring ...

Discover More

Adjusting Row Height for a Number of Worksheets

Adjusting the height of a row or range of rows is relatively easy in Excel. How do you adjust the height of those same rows ...

Discover More

Determining the Name of the Day of the Week

What day of the week is that date? Sheets can let you know by using the TEXT function to return a weekday name for a ...

Discover More

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!

More ExcelTips (menu)

Swapping Two Strings

Strings are used quite frequently in macros. You may want to swap the contents of two string variables, and you can do so by ...

Discover More

Converting Text Case

Ever notice that if someone types in all CAPS, it appears they are shouting? If your worksheets include lots of text, you may ...

Discover More

Pulling Cell Names into VBA

Excel allows you to define names that can refer to either ranges of cells or to constant information, such as formulas. If ...

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 6Mpixels. 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 nine more than 8?

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.