PDA

View Full Version : Solved: Turning multiple quantities into singles



gd4869
07-25-2012, 02:26 AM
I have an Excel sheet with a list of sizes and the quantities of each size.
In order to print labels for each part, I need to turn all quantites greater than 1 into singles.

example:
2 200 300
1 400 150

needs to be changed to this

1 200 300
1 200 300
1 400 150

The actual list is 60-70 items and there are 40 different lists
Anyone have any ideas??

Bob Phillips
07-25-2012, 03:12 AM
Select all the values in column 1, type 1 and hit Ctrl-Enter together.

gd4869
07-25-2012, 05:48 AM
That changes all the values in the first column to one, but I need to have a new entry written for values>1 ie: If the first column has a 3 in it, then I need 3 new rows with a 1

Bob Phillips
07-25-2012, 11:02 AM
Sorry, didn't read it properly

Public Sub ProcessData()
Dim LastRow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "A").Value > 1 Then

.Rows(i).Copy
.Rows(i + 1).Resize(.Cells(i, "A").Value - 1).Insert
End If
Next i

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1").Resize(LastRow).Value = 1
End With

Application.ScreenUpdating = True
End Sub

gd4869
07-25-2012, 01:32 PM
Thanks for that, exactly what I was after:yes