I generated 40 unique numbers between 1 and 1000, then sorted them. Put the
number 1 in cell A2, the remainder of the numbers were in 3 to 42 (highest
number was 851).

Ran the first macro posted by Trevor and it worked fine.

--
Regards,
Tom Ogilvy


Trevor Shuttleworth wrote in message
...
Gareth

works for me. Tested up to 10 numbers missing. Fills the gaps and adds a
0
in column B.

What doesn't work for you ? The only thought is that the data in column A
is not being seen as numeric. If I put a single quote in front of the
numbers they then appear left aligned but that really screws up the
calculations. The code then appears to loop but it is actually working
and
adding lots of rows of data.

Try it with an additional test for numeric data:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Not Application.WorksheetFunction.IsNumber(Range("A" &
i).Value)
Then
MsgBox "Error in data"
Exit Sub
End If
If Range("A" & i).Value < Range("A" & i - 1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub


Regards

Trevor


"Gareth" wrote in message
...
Trevor

I cannot seem to get your code to work at all, just to recap:

I want a macro to insert all numbers in ascending order in column A,
given
that certain numbers may be missing. Against these added numbes I would
like a 0 entered into column B.

There may be 3, 4, 5, 6, 7, 8 ..... numbers missing.

Gareth

"Trevor Shuttleworth" wrote in message
...
Gareth

one way:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Range("A" & i).Value < Range("A" & i - 1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Gareth" wrote in message
...
I have 2 columns of data, A2:A? contains week numbers in ascending
order
and
B2:B? contains orders received in that week.

I now have to put this date into a chart. This is no problem but
there
are
some week numbers missing, want I want is a little macro to add in
the
missing numbers and enter a 0 in colun B . For example:

Column A Column B
Week No. Orders
1 23
2 29
5 23
6 56
8 55


This should look like:

Column A Column B
Week No. Orders
1 23
2 29
3 0
4 0
5 23
6 56
7 0
8 55

Thaks in advance.

Gareth