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

    Unanswered: Exporting data falling outside a specified range

    Hi.

    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!!

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi IforgetIt

    Here is a bit of VBA That will help

    Code:
    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

    HTH
    Dave

Posting Permissions

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