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