adding rows conditionally with vba

[indesignkat]

Hello wonderful Chandoovians,

I have a spreadsheet I send out to ~50 people, many of whom would need special training to filter the results much less get where they need to in a pivot table, so I need to stick to the format they're accustomed to. At least for now, maybe someday I can get them to review a nice pivot table with slicers and whatnot, but that's a long term goal. Here are the things I'm trying to automate:

1) if location code on this line is different from the line above it, add 3 empty rows below this line.

2) if order # on this line is different from the line above it, add 1 empty row below this line.

3) if there are more than 1 rows with this order #, add an extra empty row below the last one with this order #.

Here's what I have now:

Sub SplitPO()
Dim r As Range, i%
Set r = Range("a3:p9999")

With r
For i = .Rows.Count To 2 Step -1
    If .Cells(i, 1) <> .Cells(i - 1, 1) Then
    .Cells(i, 1).EntireRow.Insert
    .Cells(i, 1).EntireRow.Insert
    .Cells(i, 1).EntireRow.Insert
    End If
    If .Cells(i, 7) <> .Cells(i - 1, 7) Then
    If .Cells(i, 16) > 1 Then
    .Cells(i, 1).EntireRow.Insert
    End If
    End If
    If .Cells(i, 7) <> .Cells(i - 1, 7) Then
    .Cells(i, 1).EntireRow.Insert
    End If
Next
End With

Where column A contains the location code, column G contains the order # and column P contains a countif formula that shows how many rows have this same order #.

It works, mostly, but for some reason I randomly see single-line orders with 2 empty rows after them and somewhere nearby a multi-line order with only 1 empty row after. This only seems to happen about 10-15% of the time, so I'm having trouble figuring out why it's doing that.

Ideal additions if I knew how to automate them:

1) Put an autosum in column E on the first empty row after a multi-line order

2) bold column E on single-line orders and on the autosum rows for multi-line orders, but not individual rows of a multi-line order

3) add yellow cell color in column E to any positive numbers, but only on the rows bolded by previous addition

The ideal additions are would-be-nice, but I'm mostly looking for suggestions on my vba as to why it's not entirely working as expected.

Thanks!
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Luke M]

Can you upload an example of your workbook? Very hard to work through this using only a description.

 

[indesignkat]

sure, see attached.

 

[Luke M]

Here's the corrected code.

Sub SplitPO()
Dim r As Range, i%
Dim lastRow As Long

'what data to work with?
With Worksheets("Source")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   
    Set r = .Range("a3:p" & lastRow)
End With

Application.ScreenUpdating = False
With r
    For i = .Rows.Count To 1 Step -1
        If .Cells(i, "A").Value <> .Cells(i + 1, "A").Value Then
            If .Cells(i, "P") > 1 Then
                .Cells(i + 1, 1).EntireRow.Resize(4).Insert
            Else
                .Cells(i + 1, 1).Resize(3).EntireRow.Insert
            End If
        ElseIf .Cells(i, "G").Value <> .Cells(i + 1, "G").Value Then
            If .Cells(i, "P") > 1 Then
                .Cells(i + 1, 1).EntireRow.Resize(2).Insert
            Else
                .Cells(i + 1, 1).EntireRow.Insert
            End If
        End If
    Next i

End With
Application.ScreenUpdating = True
End Sub

 

[indesignkat]

all hail the Ninja. thank you!