Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Deleting Every X Rows

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.

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.

Related Tips:

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!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Chris JG    17 Sep 2015, 04:56
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.
Dave Kerr    11 Apr 2013, 05:50
Hi Andrew,

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

All the best,
Dave
Andrew McQuillen    11 Apr 2013, 03:27
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
Dave K    05 Apr 2013, 07:34
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
Andrew McQuillen    04 Apr 2013, 08:11
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
Andrew McQuillen    04 Apr 2013, 08:09
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
Dave K    03 Apr 2013, 05:56
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
Andrew McQuillen    02 Apr 2013, 08:31
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
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.