i have two columns
5 9
15 19
20 29
i need for each row to show the numbers in the range
5
6
7
8
9
15
16
..
..
pls help

this worked ok for me.

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim myCell As Range
Dim myRng As Range
Dim oRow As Long
Dim HowMany As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

oRow = 1
For Each myCell In myRng.Cells
HowMany = myCell.Offset(0, 1).Value - myCell.Value + 1
With NewWks.Cells(oRow, "A").Resize(HowMany, 1)
.Formula = "=row(a1)-1 +" & myCell.Value
.Value = .Value
End With
oRow = oRow + HowMany
Next myCell

End Sub



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

arsovat wrote:

i have two columns
5 9
15 19
20 29
i need for each row to show the numbers in the range
5
6
7
8
9
15
16
.
.
pls help

--

Dave Peterson

Thanks it's worked. Now i have to re-study macros and VB :)

"Dave Peterson" wrote:

this worked ok for me.

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim myCell As Range
Dim myRng As Range
Dim oRow As Long
Dim HowMany As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

oRow = 1
For Each myCell In myRng.Cells
HowMany = myCell.Offset(0, 1).Value - myCell.Value + 1
With NewWks.Cells(oRow, "A").Resize(HowMany, 1)
.Formula = "=row(a1)-1 +" & myCell.Value
.Value = .Value
End With
oRow = oRow + HowMany
Next myCell

End Sub



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

arsovat wrote:

i have two columns
5 9
15 19
20 29
i need for each row to show the numbers in the range
5
6
7
8
9
15
16
.
.
pls help

--

Dave Peterson