Insert row between category breaks!!

[barleytea]

Hey gang,

I'm a super newbie on this VBA coding but I need help in doing the below.

I have data that looks like this

imgur: the simple image sharer

I need to create a macro that will insert a row that breaks up each BS row(column A).

For example,

BS
Jb
Jb
Jb

BS
Jb
Jb
Jb
jb
jb

BS
jb

BS
jb
jb

etc

Here's what I have done that I compiled from muitiple pages but it still doesn't work:
Sub insertspace()
Dim rng As Range
Dim i As Integer
Dim lastrow As Integer
Dim s As Integer
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = 1 To lastrow
If Cells(i, 1).Value = "BS" Then

ActiveCell.Offset(1, 0).EntireRow.Insert
Exit For
End If
i = i + 1
s = ActiveCell.Address
Next i
s = ActiveCell.Address

 

[Snakehips]

barleytea,

Welcome to MrExcel.

Assuming that your data set is not huge then this simple loop should do it.

Sub Insert_Rows()
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 6 Step -1
If Cells(r, 1) = "BS" Then Cells(r, 1).EntireRow.Insert
Next r
End Sub

Hope that helps.

 

[barleytea]

Thanks so much!! That worked..a side question how can I have add a subtotal to each block of BS entries?

 

[Snakehips]

barleytea,

It is not clear to me which columns other than B require a sub-total.

In the code below, see the note regarding the variable 'c' and change it to suit your needs.
c = 1 will put a sub-total formula in column B
c = 2 will put formula in column B and column C etc

Sub Insert_Rows()


'*** c = number of columns to take sub-total formula
'c = 1 will do just column B
'c = 2 wil do columns B & C  etc etc


c = 2   '<<<<<<****  Edit c here to suit!!!!


lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
For r = lr To 5 Step -1
If Cells(r, 1) = "BS" Then
If r > 5 Then
Cells(r, 1).EntireRow.Insert
Else
x = 1
End If


Cells(lr + 1 - x, 2).Resize(1, c).FormulaR1C1 = "=SUM(R[-" & (lr - r + x) & "]C:R[-1]C)"
lr = r
End If
Next r
End Sub