Insert Rows on Condition (excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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: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

Reply | Quote

Post deleted by JailAdmin

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 (;;, 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.

Reply | Quote

Fixed, see edited post. Of course HansV wrote an alternative while I was enjoying my slumber.

Reply | Quote