Insert Row Depending on Cell Vale

XfortunaX

New Member
Joined
Aug 28, 2014
Messages
28
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,
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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(n) And ActiveCell.Offset(1, 0).Value < Breaks(n) 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
******************************************************************************
 
Upvote 0
Akmatz, that was perfect! Works great,thank you!

Sorry, I should have stated A2 in my post.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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
Back
Top