Insert rows

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

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
 
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
 
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
 
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
 
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.
 
Tom and Trevor

I now know what the problem is. The data in column A is
imported from an Access query and is not seen by Excel as
a number.

The data is made up of week numbers calculated thus:

=Format([Orderdate],"ww")

Is there a way to 'make' it a number, either in Access or
in Excel before running the macro?

Gareth
 
Gareth

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
' Convert data to numbers from "text"
LastRow = Range("A65536").End(xlUp).Row
For i = 2 To LastRow
Range("A" & i).Value = Range("A" & i).Value
Next 'i
' Insert new lines with zero values
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 said:
Tom and Trevor

I now know what the problem is. The data in column A is
imported from an Access query and is not seen by Excel as
a number.

The data is made up of week numbers calculated thus:

=Format([Orderdate],"ww")

Is there a way to 'make' it a number, either in Access or
in Excel before running the macro?

Gareth
-----Original Message-----
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


the gaps and adds a
0 actually working
and (Range("A" &
i).Value) order in column A,
given little macro to add in
the


.
 
Gareth

possibly a quicker way to convert the cells to numeric:

Range("IV65536").Copy ' pick an empty cell
Range("A2:A" & LastRow).PasteSpecial _
Paste:=xlAll, _
Operation:=xlAdd, _
SkipBlanks:=False, _
Transpose:=False

Regards

Trevor


Gareth said:
Tom and Trevor

I now know what the problem is. The data in column A is
imported from an Access query and is not seen by Excel as
a number.

The data is made up of week numbers calculated thus:

=Format([Orderdate],"ww")

Is there a way to 'make' it a number, either in Access or
in Excel before running the macro?

Gareth
-----Original Message-----
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


the gaps and adds a
0 actually working
and (Range("A" &
i).Value) order in column A,
given little macro to add in
the


.
 
Back
Top