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!