Insert row between category breaks!!

barleytea

New Member
Joined
Sep 29, 2014
Messages
2
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
barleytea,

Welcome to MrExcel.

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

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

New Member
Joined
Sep 29, 2014
Messages
2
Thanks so much!! That worked..a side question how can I have add a subtotal to each block of BS entries?
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
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

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,877
Members
409,668
Latest member
mitunsLax

This Week's Hot Topics

Top