VBA to Insert Blank Row Based on Two Conditions

[nirvehex]

Hello,

I have a code:

Dim LRow As Long
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For LRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 3 Step -1
        If Cells(LRow, "B") <> Cells(LRow - 1, "B") And Cells(LRow - 1, "AA") = "T" Then
           If Cells(LRow - 2, "B") = Cells(LRow - 1, "B") Then
           'Then Account has more then one row so T as the only option is not possible
           Else
           'T is the only option so insert the row
           Rows(LRow).EntireRow.Insert
           End If
        End If
    Next LRow
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

This code counts the number of cells in column B.

Then it goes to the bottom of column B, working its way up one by one and says if there is a change in values found in column B and the value in AA is "T" then insert a blank row underneath it. At least this is the best way I can describe it (might need some help explaining better what this code does).

Anyway, I'm trying to come up with a similar code that does the following:

I'm trying to get the code to just insert a line after the last row with a T in column AA it for matching values in column B. Then continue up column B identifying the next set of matching values, find the last corresponding row with a T in column AA and insert a line underneath it.

Here's an example of what the code would do to a set of data.


For example:

Col. B: Column AA:
Loc001 TV
Loc002 T
Loc002 T
insert line here
Loc003 XRT
Loc004 T
Loc004 T
Loc004 T
insert line here
Loc005 FFT
Loc006 T
insert line here
Loc007 T
insert line here
Loc008 T
Loc008 T
would not insert a line here because Loc008 does not have only "T"
Loc008 XRT
Loc009 T
insert line here
Loc010 FRT

So it always inserts a line after the last T for the same location if and only if there is only T's in that set of locations. Any ideas?

Thank you!

Note this is cross posted at http://www.mrexcel.com/forum/excel-q...ml#post4053172

[nirvehex]

Someone gave me this code:

Sub nirvehex()
Dim i As Long
For i = Range("B" & Rows.Count).End(3).Row To 2 Step -1
        If Cells(i, "B") <> Cells(i + 1, "B") And Cells(i, "AA") = "T" Then
            Rows(i + 1).Insert
        End If
Next i
End Sub

It almost works.

The problem is:

It only works partially. For example:

Col. B Col. AA
Loc001 T
the code inserts a line here which is correct
Loc002 T
Loc002 RSS
the code does not insert a line here which is correct
Loc003 T
Loc003 T
i need a line inserted here, but the code does not do that

I'm trying to get a code that always inserts a line after the LAST T for the same location IF AND ONLY IF there are only T's in that set of locations. Any other thoughts or ideas on how to modify the code to do that?
Thanks!

[nirvehex]

I'm so sorry. This was user error. The code provided does work!