List numbers between two numbers

WESTERNWALL

Board Regular
Joined
Oct 8, 2002
Messages
204
Office Version
  1. 365
Platform
  1. MacOS
Sounds weird, doesn't it?

I have two numbers, for example 1-5, 40-150, etc. I would like to list the numbers between those 1 to 5 or 40 to 150 including the first and the last, either somewhere else on the worksheet or on another tab. Is that possible?

Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
WESTERNWALL,

Sounds weird, doesn't it?

No, just unique.


1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


3. What is the worksheet name that contains the raw data?

4. What cell contains 1-5?

5. What cell contains 40-150?
 
Upvote 0
WESTERNWALL,



No, just unique.


1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


3. What is the worksheet name that contains the raw data?

4. What cell contains 1-5?


5. What cell contains 40-150?


Excel 2010. Windows 2007. Using PC. Sheet1. Cells A1=1 and B1=5. Cells A2=40 & B2=150. Cells A3=100 & B3=1000. Those numbers are not static. They can be changed. For instance, if I change A1 to 25 and A2 to 75 The list of numbers will change accordingly.

1
2
3
4
5

or

25
26
27
|
|
|
75

The numbers on Sheet are in two columns one beneath the other. The resultant list of numbers of each set will appear wherever they are configured to appear.

Hope that's clearer?

Thanks
 
Upvote 0
This macro would do what you want for cells A1 to cells B1 in the first worksheet you have, in this example 1 and 5

let me know if you have questions

Code:
Sub listnums()



strt = Sheets(1).Cells(1, 1).Value
endi = Sheets(1).Cells(1, 2).Value
On Error GoTo delinfo
Worksheets.Add(After:=Worksheets(1)).Name = strt & "-" & endi ' Creates a sheet called 1-5 and puts info 'there. 
On Error GoTo 0
continu:


Set s2 = Sheets(strt & "-" & endi)


For i = strt To endi
s2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = i
Next i


Rows(1).Delete


Exit Sub


delinfo:                                                            'error handling section :)
Sheets(strt & "-" & endi).Cells.Clear
Application.DisplayAlerts = False
Sheets(2).Delete
GoTo continu


End Sub
 
Upvote 0
WESTERNWALL,

If I understand you correctly:

Sample raw data in worksheet Sheet1:


Excel 2007
AB
115
240150
31001000
4
Sheet1


After the macro in a new worksheet Results (not all rows are shown for brevity):


Excel 2007
ABC
1140100
251501000
3seriesseriesseries
4140100
5241101
6342102
7443103
8544104
945105
113149209
114150210
115211
903999
9041000
905
Results


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub ExpandNumberSeries()
' hiker95, 08/21/2014, ME800450
Dim w1 As Worksheet, wr As Worksheet
Dim a As Variant, i As Long
Dim r As Long, lr As Long, nc As Long, c As Range
Dim MyStart As Long, MyStop As Long, n As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
lr = w1.Cells(Rows.Count, 1).End(xlUp).Row
a = w1.Range("A1:B" & lr).Value
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Sheets("Results")
With wr
  .UsedRange.ClearContents
  For i = 1 To lr
    nc = nc + 1
    .Cells(1, nc).Value = a(i, 1)
    .Cells(2, nc).Value = a(i, 2)
  Next i
  For Each c In .Range(.Cells(1, 1), .Cells(1, lr))
    MyStart = .Cells(1, c.Column)
    MyStop = .Cells(2, c.Column)
    n = (MyStop - MyStart) + 1
    .Cells(3, c.Column) = "series"
    .Cells(4, c.Column) = MyStart
    With .Range(.Cells(4, c.Column), .Cells(n + 3, c.Column))
      .DataSeries Step:=1, Stop:=MyStop
    End With
  Next c
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExpandNumberSeries macro.
 
Upvote 0
Just list of numbers like

1
2
3
4
5
on Sheet1

or

40
41
42
|
|
|
|
150

on some other sheet.

Thanks

Hi,

Code:
Sub Fill()
    [A4] = [A1].Value
    ato = [B1].Value
    [B4] = [A2].Value
    bto = [B2].Value
    [C4] = [A3].Value
    cto = [B3].Value
    [A4].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=ato
    [B4].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=bto
    [C4].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=bto
End Sub
 
Upvote 0
Useful,

A very nice solution!!!

May I point out the need for one minor correction.........

Rich (BB code):
Sub Fill()    [A4] = [A1].Value
    ato = [B1].Value
    [B4] = [A2].Value
    bto = [B2].Value
    [C4] = [A3].Value
    cto = [B3].Value
    [A4].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=ato
    [B4].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=bto
    [C4].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=cto
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

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