Bernie,

Now you are just showing off ;)

Thank you, the five minutes was more than acceptable but I will give your
suggestion a go next month when I get more of these sheets to work on.

Thank you very much for your help.

John

"Bernie Deitrick" wrote:

You are genius. Your marco has done 5 sheets containing about 15k rows of
data on each in about 5 minutes!

<Blush

I didn't realize that you has such big data sets. It might be faster if you used this at the top of
the code:

With Application
.ScreeenUpdating = False
.EnableEvents = False
End With

and this at the bottom of the code

With Application
.ScreeenUpdating = True
.EnableEvents = True
End With

And it might be faster still if we turned off automatic calculation, and filled in the numbers using
code instead of the formula...

HTH,
Bernie
MS Excel MVP


"mg_sv_r" wrote in message
...
Bernie,

You are genius. Your marco has done 5 sheets containing about 15k rows of
data on each in about 5 minutes!

Without this, I doubt I would have got this done! Thank you very much!

John

"Bernie Deitrick" wrote:

John,

You could use a macro: try the macro below. It worked well in my tests.

HTH,
Bernie
MS Excel MVP


Sub JohnRowInsertMacro()
Dim myRow As Long
Dim myCount As Integer

For myRow = Range("A65536").End(xlUp).Row To 1 Step -1
If IsNumeric(Cells(myRow, 1).Value) Then
myCount = Cells(myRow, 2).Value - Cells(myRow, 1).Value
If myCount 0 Then
Rows(myRow).Copy
Rows(myRow + 1).Resize(myCount).Insert
With Cells(myRow + 1, 1).Resize(myCount)
.FormulaR1C1 = "=R[-1]C+1"
.Value = .Value
End With
End If
End If

Next myRow
Columns(2).Delete
End Sub



"mg_sv_r" wrote in message
...
Hi

I have a bit of a nightmare task on to carry out on large worksheets that I
am hoping can be automated in some way because manually this will take far
too long

The worksheets basically look like this...

-------A------- -------B------- ------C------- -------D-------
1 ---61236----- ---61239----- - a text val -- - a text val --
2 ---61531----- ---61531----- - B text val -- - B text val --

The number in column A and the number in column B represent a range for
which the values in the other columns apply. (So if a given number is between
A and B then C and D are true).

What I need to do is change this sheet so that instead of giving the number
range in columns A and B, it gives each number individually. (Instead of A
saying 61236 and B saying 61245 I need A to list 61236, 61237, 61238 etc with
all other values applicable copied down the sheet.) The example abopve would
end up looking something like this..

-------A------- ------B------- -------C-------
1 ---61236----- - a text val -- - a text val --
2 ---61237----- - a text val -- - a text val --
3 ---61238----- - a text val -- - a text val --
4 ---61239----- - a text val -- - a text val --
5 ---61531----- - B text val -- - B text val --

Is this possible or would it require some advanced programming or similar?

Thanks in advance for any help.

Regards
John