List numbers between two numbers

[WESTERNWALL]

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

 

[hiker95]

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?

 

[Oeldere]

and what should be the result.

1 2 3 4 5 or 1 3 5 or 1 1,5 2 2,5 3 etc?

 

[WESTERNWALL]

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

 

[WESTERNWALL]

and what should be the result.

1 2 3 4 5 or 1 3 5 or 1 1,5 2 2,5 3 etc?

Just list of numbers like

1
2
3
4
5
on Sheet1

or

40
41
42
|
|
|
|
150

on some other sheet.

Thanks

 

[Arithos]

In different cells I assume?

 

[Arithos]

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

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

 

[hiker95]

WESTERNWALL,

If I understand you correctly:

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B
1	1	5
2	40	150
3	100	1000
4
Sheet1

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

Excel 2007
	A	B	C
1	1	40	100
2	5	150	1000
3	series	series	series
4	1	40	100
5	2	41	101
6	3	42	102
7	4	43	103
8	5	44	104
9		45	105
113		149	209
114		150	210
115			211
903			999
904			1000
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.

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.

 

[Useful]

Just list of numbers like

1
2
3
4
5
on Sheet1

or

40
41
42
|
|
|
|
150

on some other sheet.

Thanks

Hi,

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

 

[Snakehips]

Useful,

A very nice solution!!!

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

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