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
Teur2Yv

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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,550
Office Version
  1. 365
  2. 2010
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,550
Office Version
  1. 365
  2. 2010
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,485
Messages
5,831,964
Members
430,098
Latest member
bemmelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top