Converting a range of numbers into single numbers

[Flash0220]

Hello,


I'm having the following little problem, and i'm hoping that someone here can help me solve it.

I have a sheet called "Ranges" containing the following data.

Column A Column B
1 100
2000 4444

What i would like to do is the following.

The ranges above (1 to 100 and 2000 to 4444) have to be converted
to single numbers on a sheet called "Single numbers".

So something like this.

1
2
3
4
..
..
100
2000
2001
2002
..
..
4444

Is there a simple way to do this in VBA ?
Thanks.


Flash

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long, j As Long
With Sheets("Ranges")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        For j = .Range("A" & i).Value To .Range("B" & i).Value
            Sheets("Single numbers").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = j
        Next j
    Next i
End With
End Sub

 

[Flash0220]

Hello,


Thanks for the fast reply.
Works perfectly !


Flash.

 

[Peter_SSs]

Another way (I've assumed the 'Ranges' data starts in A2 - adjust code if not)

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Single_Numbers()<br>    <SPAN style="color:#00007F">Dim</SPAN> rCel <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Worksheets("Ranges")<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rCel <SPAN style="color:#00007F">In</SPAN> .Range("A2", .Range("A" & Rows.Count).End(xlUp))<br>            <SPAN style="color:#00007F">With</SPAN> Worksheets("Single numbers").Range("A" & Rows.Count).End(xlUp).Offset(1)<br>                .Value = rCel.Value<br>                .Resize(rCel.Offset(, 1).Value - rCel.Value + 1).DataSeries Rowcol:=xlColumns, _<br>                    Type:=xlLinear, Step:=1, Stop:=rCel.Offset(, 1).Value, Trend:=<SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> rCel<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Flash0220]

Hallo,


Thanks very much for another fast answer.
Problem solved !




Flash