Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Rows (Excel 2003)

    I have a very long spreadsheet that I use as an inventory listing. Each item in the list has a bin number. I have sorted the list in bin number order to group items in the same bin together. After each group of items in a bin I want to add 3 blank rows to separate the listing to make it easier to read. How can I do this automatically without having to go thru the entire listing and insert them between bins?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Rows (Excel 2003)

    If the bin numbers are in column A and there are no header rows, then the code below should do what you ask.

    <code>
    Public Sub InsertBlankLines()
    Dim I As Long, lLastRow As Long
    lLastRow = Range("A65536").End(xlUp).Row - 1
    For I = lLastRow - 1 To 1 Step -1
    If Range("A1").Offset(I, 0).Value <> Range("A1").Offset(I + 1, 0).Value Then
    Range(Range("A1").Offset(I + 1, 0), Range("A1").Offset(I + 3, 0)).EntireRow.Insert
    End If
    Next I
    End Sub
    </code>
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Rows (Excel 2003)

    To add to what Legare has given you, you might want to consider using his code on a COPY of your data.
    I have found it prudent to keep my databases intact for future massaging.
    When I take actions like inserting blank rows, I like to extract all records to another
    sheet and take such actions on the Extracted records.
    Just my two cents worth.

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Rows (Excel 2003)

    This didn't work. What did I do wrong? I have attached a part of my spreadsheet.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Rows (Excel 2003)

    You have placed the macro in the ThisWorkbook module. Instead, you should create a new standard module (Insert | Module in the Visual Basic Editor) and place the macro there.

    The macro assumes that the bin numbers are in column A, if it is another column, you should adapt the code accordingly.

  6. #6
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Rows (Excel 2003)

    <P ID="edit" class=small>(Edited by Waggers on 02-Dec-05 10:20. Edited to add link to MS article on subtotals)</P>Another option is to use subtotals on each change in bin number. You could select something harmless to total, like count of bin numbers. There are several advantages to this:
    <UL><LI>You don't have to write any VBA code
    <LI>Excel adds handy "levels" so that you can view a summary of your data or the raw data quickly
    <LI>You can easily remove the subtotals, restoring your spreadsheet to its current state (without having to make a backup copy)[/list]Just a thought.

    (More info on subtotals: Microsoft Office Assistance: About subtotals)
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  7. #7
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Rows (Excel 2003)

    How can I simplfy this code to make so that after I select Rows 1 to X to please add one Row in btwn each row?

    Thank you

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adding Rows (Excel 2003)

    Did you try changing the line from:
    Range(Range("A1").Offset(I + 1, 0), Range("A1").Offset(I + 3, 0)).EntireRow.Insert

    to
    Range(Range("A1").Offset(I + 1, 0), Range("A1").Offset(I + 1, 0)).EntireRow.Insert

    Steve

  9. #9
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Rows (Excel 2003)

    Yes that did NOT work.

    I think this might be it but it was a guess since I don't really know VBA

    Public Sub InsertBlankLines()
    Dim I As Long, lLastRow As Long
    lLastRow = Range("A65536").End(xlUp).Row - 1
    For I = lLastRow - 1 To 1 Step -1
    Range(Range("A1").Offset(I + 1, 0), Range("A1").Offset(I + 1, 0)).EntireRow.Insert
    Next I
    End Sub

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Adding Rows (Excel 2003)

    What did it do? or what did not do?

    It presumes you have info in Col A.

    Steve

  11. #11
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Rows (Excel 2003)

    See the post starting here <post:=548,481>post 548,481</post:> for both a macro and non-macro methods.

  12. #12
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Rows (Excel 2003)

    The following will insert one row based on a selection of cells; My previous post demonstarted the insertion of rows based on entire continous table of data.


    <pre>Option Explicit

    Sub InsertBlankWithinSelection()

    Dim i As Long, eRow As Long, sRow As Long, sColumn As Long
    sColumn = Selection.Column

    sRow = Selection.Row
    eRow = Selection.Rows.Count
    For i = sRow + eRow To sRow Step -1
    Cells(i, 1).EntireRow.Select
    Selection.Insert Shift:=xlDown
    Next

    Cells(sRow, sColumn).Select

    End Sub
    </pre>


  13. #13
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Rows (Excel 2003)

    Thank you to all. This was exactly what I needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •