Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Rows on Condition (excel 2000)

    I hope someone will be able to help, I have a report in range A1:J1000 and is sorted by a part number that appears in row D. I need a macro that would insert a row each time the value in D changes.
    Thank you for any help you may be able to give,
    Stats

  2. #2
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Rows on Condition (excel 2000)

    Post deleted by JailAdmin

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Insert Rows on Condition (excel 2000)

    OK, now I'm confused ... you insert them and delete them later? Is there something we can look at related to why you insert and then delete them?

    Also, did you see the macro I added after first responding?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Rows on Condition (excel 2000)

    Thanks for your reply John. No, I am not deleting the rows after putting them in. It's a report that pulls some information from our SQL Database using MS Query and outputs a report of what products have to be produced in the near future. Each part can appear on the report one time or it can appear a dozen times depnding on how many orders are outstanding but it is sorted by the part number in Col. D. Inserting the blank row between each part makes it easier to look at.
    When I ran your code I got an "Application-defined or object-defined error". The sheet in the workbook is called "Sheet1" and I couldn't find any other "variables" I would have to change in the code. Have I missed something.

  5. #5
    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: Insert Rows on Condition (excel 2000)

    An easier solution might be to use CONDITIONAL FORMATTING to "hide" (color them the background) the duplicated parts or to segment the Part# segments with lines instead of blank rows.

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

    Re: Insert Rows on Condition (excel 2000)

    John's code runs into problems in cell D1, because it tries to refer to the cell above, which doesn't exist. And I think it's better to step through the range backwards.

    Try this:

    Sub NewRowOnChange()
    Dim rng As Range
    Dim lngRow As Long
    Set rng = Range("D1", Range("D65536").End(xlUp))
    For lngRow = rng.Rows.Count To 2 Step -1
    If rng(lngRow - 1) <> "" And rng(lngRow) <> "" And _
    rng(lngRow) <> rng(lngRow - 1) Then
    rng(lngRow).EntireRow.Insert
    End If
    Next lngRow
    End Sub

    This code steps backwards and stops at row 2. If you want to prevent the code from inserting a row above row 2 (for instance because the first row contains field names), replace 2 by 3 (or whatever you want) in the instruction For lngRow = rng.Rows.Count To 2 Step -1

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Insert Rows on Condition (excel 2000)

    Yep. Didn't test with anything in row one as I never put data or headers in row 1, and I hate writing that upside down code! <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Rows on Condition (excel 2000)

    Thank you Hans, it worked exactly as I needed it too.
    Stats

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Insert Rows on Condition (excel 2000)

    Just checking; do you want to insert this row in order to insert a subtotal or count or such? If so, Data, Subtotals has capabilities like this.

    [Edit]
    Here's code to do it if required:

    Sub NewRowOnChange()
    Dim rngCell As Range
    For Each rngCell In _
    ThisWorkbook.Worksheets("Sheet1").Columns("D").SpecialCells(xlCellTypeConstants, 3)
    If rngCell.Row > 1 Then
    If rngCell.Offset(-1, 0).Value <> "" _
    And rngCell.Value <> rngCell.Offset(-1, 0).Value Then _
    rngCell.EntireRow.Insert Shift:=xlDown
    End If
    Next rngCell
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Insert Rows on Condition (excel 2000)

    Fixed, see edited post. Of course HansV wrote an alternative while I was enjoying my slumber.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Rows on Condition (excel 2000)

    Good Day Hans,
    Thanks again for your help with the macro, but there is one unforeseen problem. Is there a way to undo the inserting of the rows when I close the workbook so when I open it the next time it's inserting the rows in the correct spots again.
    Thanks,
    Stats

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

    Re: Insert Rows on Condition (excel 2000)

    Hello Stats,

    This macro will remove rows whose cell in column D is empty:

    Sub DeleteEmptyRows()
    Dim rng As Range
    Dim lngRow As Long
    Set rng = Range("D1", Range("D65536").End(xlUp))
    For lngRow = rng.Rows.Count To 2 Step -1
    If rng(lngRow) = "" Then
    rng(lngRow).EntireRow.Delete
    End If
    Next lngRow
    End Sub

    You can execute this macro manually before closing the workbook, or call it from the Workbook_BeforeClose event, so that it will be run automatically.

  13. #13
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Insert Rows on Condition (excel 2000)

    FWIW, an alternative approach that doesn't (necessarily) use macros... Create a separate list of unique part numbers (Data | Filter | Advanced Filter); append them at the bottom of your list; make them "disappear" - either with a custom number format (;;[img]/forums/images/smilies/wink.gif[/img], or by making the font color the same as the background; then sort the newly-extended list with part number as the primary key and some other (non-blank) field as the secondary. Each new part number would then be preceded by an (apparently) blank row. Removing the rows would mean sorting by the secondary key to group together the inserted rows, deleting them, and then sorting back into part number sequence.
    The above could be made into a macro, although it would probably be more complicated than the solutions already provided which I'm sure work fine. The only potential reason for considering the above would be if you have a very large list: the above technique would run much quicker than the row insertion macros.

Posting Permissions

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