nirvehex
02-13-2015, 07:40 AM
Hello all. This is cross posted here: http://www.mrexcel.com/forum/excel-questions/835867-visual-basic-applications-insert-blank-row.html
Here's the problem I'm trying to solve with VBA:
There are two columns I'm looking at: Column B and Column AA
Column B has a Location Code. Column AA has different strings of letters.
All I'm looking to do is: insert a blank row below the last row in the same group of location codes in column B if and only if there are only T's in column AA for the same row as that set of location codes. Or put another way, if the same group of location codes (column B) only show corresponding "T"'s in column AA then insert a blank row below the last location code in that like group.
So I think code needs to (1) identify how many rows the same location codes span, then (2) check column AA to see if there are only corresponding "T", then (3) if there are only T's corresponding, then insert a blank row below the last row in that same set of Location Codes, or (4) if there are not only T's corresponding, then do nothing and move to the next group of like Location Codes.
Here's an example of my spreadsheet:
Column B Column AA
Loc001
TV
Code would not insert blank row here because there is not only "T" in column AA for this location
Loc002
T
Loc002
T
Code inserts blank row here because there are only "T" in column AA for Loc002
Loc003
XRS
Code would not insert row here because there is not only "T" in column AA for Loc003
Loc004
T
Loc004
T
Loc004
T
Code inserts blank row here because there are only "T" in column AA for Loc004
Loc005
FFT
Code would not insert a blank here because there is not only "T" in column AA for Loc005
Loc006
T
Code inserts blank row here because there are only "T" in column AA for Loc006
Loc007
T
Code inserts blank row here because there are only "T" in column AA for Loc007
Loc008
T
Loc008
T
Loc008
XRT
Code would not insert blank row here because Loc008 does not have only "T"
Loc009
T
Code inserts blank row here because there are only "T" in column AA for this location
Loc010
FRT
Loc010
T
Code would not insert blank row here because Loc010 does not have only "T" in column AA for this location
Here's my code that almost works:
Dim i As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
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
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Any help would be so much appreciated! Thank you!
Here's the problem I'm trying to solve with VBA:
There are two columns I'm looking at: Column B and Column AA
Column B has a Location Code. Column AA has different strings of letters.
All I'm looking to do is: insert a blank row below the last row in the same group of location codes in column B if and only if there are only T's in column AA for the same row as that set of location codes. Or put another way, if the same group of location codes (column B) only show corresponding "T"'s in column AA then insert a blank row below the last location code in that like group.
So I think code needs to (1) identify how many rows the same location codes span, then (2) check column AA to see if there are only corresponding "T", then (3) if there are only T's corresponding, then insert a blank row below the last row in that same set of Location Codes, or (4) if there are not only T's corresponding, then do nothing and move to the next group of like Location Codes.
Here's an example of my spreadsheet:
Column B Column AA
Loc001
TV
Code would not insert blank row here because there is not only "T" in column AA for this location
Loc002
T
Loc002
T
Code inserts blank row here because there are only "T" in column AA for Loc002
Loc003
XRS
Code would not insert row here because there is not only "T" in column AA for Loc003
Loc004
T
Loc004
T
Loc004
T
Code inserts blank row here because there are only "T" in column AA for Loc004
Loc005
FFT
Code would not insert a blank here because there is not only "T" in column AA for Loc005
Loc006
T
Code inserts blank row here because there are only "T" in column AA for Loc006
Loc007
T
Code inserts blank row here because there are only "T" in column AA for Loc007
Loc008
T
Loc008
T
Loc008
XRT
Code would not insert blank row here because Loc008 does not have only "T"
Loc009
T
Code inserts blank row here because there are only "T" in column AA for this location
Loc010
FRT
Loc010
T
Code would not insert blank row here because Loc010 does not have only "T" in column AA for this location
Here's my code that almost works:
Dim i As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
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
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Any help would be so much appreciated! Thank you!