Take the 2-minute tour ×

I would like to copy the Name and Description If Quantity on and is equal to zero so I can order the fruit that I do not have. It starts from A2 whenever it see "STOP" then it stops.

enter image description here

I have the code: enter image description here

However, the code does not stop by itself, I have to break it when it runs. It copies all empty rows between and entire row when I only need to have first 2 columns. My questions are 1. how to let the program knows it should stop when see "STOP" at column A? Only copy none empty row? and only copy first two column (or the number of columns that I would like to have)?

share|improve this question
a word of advise - don't post images of code, post the code itself (formatted as code). Many people (me included) won't bother trying your code if we have to type it out –  chris neilsen May 19 '12 at 22:13
+ 1 Nicely explained :) I agree with Chris here. It really becomes tedious to retype the code ;) –  Siddharth Rout May 19 '12 at 23:36
add comment

1 Answer

up vote 2 down vote accepted
  1. Stop the loop
    The problem is in your While clause: you are comparing the length of a string to a string - that will never be TRUE

  2. Copy only non-empty rows
    Test for valid data

  3. Copy required number of columns
    See sample code

FWIW your code can do with a bit of improving - heres my take on your requirements

Sub CopySample()
    Dim shSrc As Worksheet
    Dim shDst As Worksheet
    Dim rSrc As Range
    Dim rDst As Range
    Dim numCol As Long ' number of columns to copy

    On Error GoTo EH

    numCol = 2

    ' select source and dest sheets
    Set shSrc = ActiveWorkbook.Worksheets("Sheet1")
    Set shDst = ActiveWorkbook.Worksheets("Sheet2")

    ' Select initial rows
    Set rSrc = shSrc.Cells(2, 1)
    Set rDst = shDst.Cells(23, 1)

    ' loop over source
    Do While rSrc <> "STOP"
        ' Test Source row, Qty = 0 and Name is not blank
        With rSrc
            If .Offset(0, 2) = 0 And .Value <> "" Then
                .Resize(1, numCol).Copy rDst.Resize(1, numCol)
                Set rDst = rDst.Offset(1, 0)
            End If
        End With
        Set rSrc = rSrc.Offset(1, 0)
Exit Sub
    MsgBox "Error " & Err.Description
End Sub

Note that looping over a range can be slow for large amounts of data. Probably OK in this case, but there are ways to improve speed.

share|improve this answer
Thank you for the code and details explanation –  NCC May 19 '12 at 22:45
+ 1 Chris. Nicely done (and explained...) –  Siddharth Rout May 19 '12 at 23:36
add comment

Your Answer


