Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004

    Unanswered: Exporting data falling outside a specified range


    I have a worksheet which displays rows of analytical results under the same headings. For each heading there is a specification (eg. must be < 5 etc.) When any of the results for a row falls outside the specifications I want Excel to automatically export that row to another worksheet. For example if I had 10 rows of results and it displayed 3 rows having out of spec results those three rows would be copied and exported to the other worksheet and any susequent results added to the first worksheet that are out of spec would be added below the 3 original out of specs on the second worksheet. Basically can this be done and if so what function do I need to use?? I'd appreciate any help on this!!


  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    Hi IforgetIt

    Here is a bit of VBA That will help

    Sub test2()
        Dim lngLastRow As Long
        Dim i As Long
        'Find the last row of data Using Column A
        lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
        For i = 2 To lngLastRow
            'pick cell (Column A) and test value of that cell
            If Cells(i, 1).Value > 5 Then
                'if test = true then copy the row
                Cells(i, 1).EntireRow.Copy
                'paste it in a seperate worksheet
                Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
            End If
        Next i
        Application.CutCopyMode = False
    End Sub
    you will have to modify this for each of your tests and alter the columns to test the correct ones,

    IF you require any more help post back and ill help you through with this,
    the other option may be to do this manuallywith an autofilter


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts