I have a sheet with ( amongst other data) a column containing a series of
prices in ascending order.

I need code to seperate them into ranges such a £0-£4000, $4001 - £6000,
£6001-£8000 and so on to £20,000. I would like to insert three blank rows
after each range so that I can add summary information on each range into
these rows.

Can you help please?

Assume you numbers are in column C and C1 contains a label.

Sub Insertrows()
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Cat1 = (Cells(lastrow, "C") - 1) \ 4000
For i = lastrow To 2 Step -1
Cat = (Cells(i, "C") - 1) \ 4000
If Cat < Cat1 Then
Cells(i + 1, 1).Resize(3).EntireRow.Insert
End If
Cat1 = Cat
Next
End Sub

--
Regards,
Tom Ogilvy


"Alan M" wrote:

I have a sheet with ( amongst other data) a column containing a series of
prices in ascending order.

I need code to seperate them into ranges such a £0-£4000, $4001 - £6000,
£6001-£8000 and so on to £20,000. I would like to insert three blank rows
after each range so that I can add summary information on each range into
these rows.

Can you help please?

Hi Tom,


Thanks for the help. The code you sent inserts rows below the 0-4000 range.
However I also need it torun throught the column of prices and insert rows
below the 4001-6000 prices, the 6001- 8000 prices and so on so I have several
rows of prices in each range separated by three blank rows.

"Tom Ogilvy" wrote:

Assume you numbers are in column C and C1 contains a label.

Sub Insertrows()
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Cat1 = (Cells(lastrow, "C") - 1) \ 4000
For i = lastrow To 2 Step -1
Cat = (Cells(i, "C") - 1) \ 4000
If Cat < Cat1 Then
Cells(i + 1, 1).Resize(3).EntireRow.Insert
End If
Cat1 = Cat
Next
End Sub

--
Regards,
Tom Ogilvy


"Alan M" wrote:

I have a sheet with ( amongst other data) a column containing a series of
prices in ascending order.

I need code to seperate them into ranges such a £0-£4000, $4001 - £6000,
£6001-£8000 and so on to £20,000. I would like to insert three blank rows
after each range so that I can add summary information on each range into
these rows.

Can you help please?

Sub Insertrows()
Dim lastrow As Long, Cat1 As Long
Dim Cat As Long
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Cat1 = (Cells(lastrow, "C") - 1) \ 2000
If Cat1 < 1 Then Cat1 = 1
For i = lastrow To 2 Step -1
Cat = (Cells(i, "C") - 1) \ 2000
If Cat < 1 Then Cat = 1
If Cat < Cat1 Then
Cells(i + 1, 1).Resize(3).EntireRow.Insert
End If
Cat1 = Cat
Next
End Sub

--
Regards,
Tom Ogilvy


"Alan M" wrote:

Hi Tom,


Thanks for the help. The code you sent inserts rows below the 0-4000 range.
However I also need it torun throught the column of prices and insert rows
below the 4001-6000 prices, the 6001- 8000 prices and so on so I have several
rows of prices in each range separated by three blank rows.

"Tom Ogilvy" wrote:

Assume you numbers are in column C and C1 contains a label.

Sub Insertrows()
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Cat1 = (Cells(lastrow, "C") - 1) \ 4000
For i = lastrow To 2 Step -1
Cat = (Cells(i, "C") - 1) \ 4000
If Cat < Cat1 Then
Cells(i + 1, 1).Resize(3).EntireRow.Insert
End If
Cat1 = Cat
Next
End Sub

--
Regards,
Tom Ogilvy


"Alan M" wrote:

I have a sheet with ( amongst other data) a column containing a series of
prices in ascending order.

I need code to seperate them into ranges such a £0-£4000, $4001 - £6000,
£6001-£8000 and so on to £20,000. I would like to insert three blank rows
after each range so that I can add summary information on each range into
these rows.

Can you help please?