seperating data

[Alan M]

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?

[Tom Ogilvy]

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?

[Alan M]

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?

[Tom Ogilvy]

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?