PDA

View Full Version : [SOLVED] VBA to Insert Blank Row Based on Two Conditions



nirvehex
01-23-2015, 10:28 AM
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-questions/830969-visual-basic-applications-insert-blank-row-based-two-conditions.html#post4053172

nirvehex
01-29-2015, 01:26 PM
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
01-29-2015, 01:36 PM
I'm so sorry. This was user error. The code provided does work!