Create a custom range number list till max input

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
Using Excel 2010
Hello,

Here below in the sheet I have shown 3 examples but need macro or formula which I want to create a custom range number list till max input….
Example1…D3 max range 890 friction 50 create range from 1 to 890 in range C6:E23

Example2…H3 max range 1249 friction 75 create range from 1 to 1249 in range G6:I22

Example2…L3 max range 930 friction 100 create range from 1 to 930 in range K6:M15

Note: I want a formula or a macro which can be used in Excel 2000 also.
Here is an example sheet with expected results…

Sort By Range.xls
ABCDEFGHIJKLMN
1
2Max RangeMax RangeMax Range
38901249930
4Friction 50Friction 75Friction 100
5
61To501To751To100
751To10076To150101To200
8101To150151To225201To300
9151To200226To300301To400
10201To250301To375401To500
11251To300376To450501To600
12301To350451To525601To700
13351To400526To600701To800
14401To450601To675801To900
15451To500676To750901To930
16501To550751To825
17551To600826To900
18601To650901To975
19651To700976To1.050
20701To7501.051To1.125
21751To8001.126To1.200
22801To8501.201To1.249
23851To890
24
Sheet1


Regards,
Moti
 
That's what I love about this sight. I come up with a program that uses about 30 lines of code and an A+ student does the same thing with 10 lines of code. I am adding Peter's program to my toolbox.
 
Upvote 1

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
A little risky in my opinion as if there was no previous calculation they delete everything in that header area.
I would also recommend not using one of vba's standard words as a variable name (eg Count) as that can sometimes lead to disastrous results.


Here is another option with no Dim statements at all & no looping to create each row individually.
This also deletes any previous calculation but preserves the heading section if no prior calc.

VBA Code:
Sub CreateCustomRange()
  Range("C1", Range("E" & Rows.Count).End(xlUp)).Offset(5).Clear
  With Range("C6:E6").Resize(Range("D3").Value / Range("E4").Value + 1)
    .Formula = Array("=C5+E$4", "To", "=min(E5+E$4,D$3)")
    .Cells(1).Value = 1
    .Value = .Value
    .Columns(2).HorizontalAlignment = xlCenter
    .Font.Bold = True
    If .Cells(.Rows.Count, 1).Value > Range("D3").Value Then .Rows(.Rows.Count).Clear
  End With
End Sub

Example:

motilulla.xlsm
CDE
1
2Max Range
348000000
4Friction13983816
5
61To13983816
713983817To27967632
827967633To41951448
941951449To48000000
10
Sheet4
Hello Peter_SSs, thank you for looking into #Post10 code and giving a best advice and making a shorter and safer macro it in not just shorter but quick also I guess it is because of no looping. 👌

Peter_SSs, You are a star. 🤝 I have adapted your code to work with it. 🍻

I sincerely appreciate your caring help. I wish you Good Luck!

Kind Regards,

Moti :)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

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