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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.