Excel: Copy the value in Column B the number of times indicated by the value in Column A in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, if I have a list of quantities in Column A and a list of product names in column B, how can I create a Macro to copy the product names the number of times indicated by the quantity in Column A?

So in Column C, I would have the product names listed in a row the number of times that is given by the quantity.

Answer: Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, we have our quantities listed in Column A and our product names listed in Column B.

To run the macro, select Macro > Macros under the Tools menu.

When the Macro window appears, select the macro called CopyToColumnC and click on the Run button.

When the macro has completed, the above message box will appear.

You should now see the product name appear in Column C, the number of times based on the value in the quantity column. In this case, Mr. Clean appears 2 times, Bounty towels appears 5 times, and Tide detergent appears 1 time.

You can press Alt-F11 to view the VBA code.

Macro Code:

The macro code looks like this:

Sub CopyToColumnC()

    Dim LRow As Integer
    Dim LQty As Integer
    Dim LProduct As String
    Dim LColCPosition As Integer
    Dim j As Integer
    Dim LStart As Integer
    Dim LEnd As Integer

    'Search for values in column B starting at row 2
    LRow = 2

    'Copy values to column C starting at row 2
    LColCPosition = 2

    'Search through values in column B until a blank cell is encountered
    While Len(Range("B" & CStr(LRow)).Value) > 0

        'Retrieve quantity and product name
        LQty = Range("A" & CStr(LRow)).Value
        LProduct = Range("B" & CStr(LRow)).Value

        'Set start and end position for copy to column C
        LStart = LColCPosition
        LEnd = LColCPosition + LQty

        'Copy product name the number of times that is given by the quantity
        For j = LStart To LEnd - 1
            Range("C" & CStr(j)).Value = LProduct
        Next

        'Update column C position
        LColCPosition = LEnd

        LRow = LRow + 1

    Wend

    MsgBox "Column C has been populated."

End Sub