Insert Row Depending on Cell Vale

[XfortunaX]

Hi,

I have a random list of numbers in column A and I would like to sort and insert rows and titles between certain number breaks.

For example: Sort column from Z to A
Insert a row between 0 & 59,000 and have 0-59K in bold in the inserted row
then
Insert a row between 60,000 & 99,000 and have 60-99K in bold in the inserted row
then
Insert a row between 100,000 & 199,000 and have 100-199K in bold in the inserted row
Then
Insert a row between 200,000 & 400,000 and have 200-400K in bold in the inserted row
Then
Insert a row 400+ and have 400K+ in bold in the inserted row

816,122.75
252,551.21
223,920.09
111,384.04
73,911.26
66,425.14
66,222.48
43,187.06
21,407.96
12,251.04
11,170.55


I am using Excel 2010. In principle it seems very simple. However, I can get the list to sort and I am about stumped after that.

any help would be greatly appreciated.

Thanks,

 

[akmatz]

What cell is your very first value in? (816k)

 

[akmatz]

Try this on a COPY of your data, NOT the original.
Assumes the first value is in cell A2.
Assumes that the data is already sorted. (You said that you could do this yourself.)

***************************************************************************
Sub XfortunaX()

Dim Breaks(4) As Double
Breaks(1) = 400000
Breaks(2) = 200000
Breaks(3) = 100000
Breaks(4) = 60000




Range("A2").Select
Selection.EntireRow.Insert
ActiveCell.Value = "400k+"
Selection.Font.Bold = True
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Value = ""
For n = 1 To 4
If ActiveCell.Value > Breaks And ActiveCell.Offset(1, 0).Value < Breaks Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Select Case n
'Case 1
'ActiveCell.Value = "400k=+"
Case 1
ActiveCell.Value = "200-400k"
Case 2
ActiveCell.Value = "100-199k"
Case 3
ActiveCell.Value = "60-99k"
Case 4
ActiveCell.Value = "0-59k"
Case Else
ActiveCell.Value = "Undefined"
End Select
Selection.Font.Bold = True
End If
Next n

ActiveCell.Offset(1, 0).Select
Loop

End Sub
******************************************************************************

 

[XfortunaX]

Akmatz, that was perfect! Works great,thank you!

Sorry, I should have stated A2 in my post.

Thanks!

 

[akmatz]

My pleasure. Thanks for using the forum.