Exporting data falling outside a specified range

iforgetit · #1 (**permalink**) 11-29-04, 10:49

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

DavidCoutts · #2 (**permalink**) 11-30-04, 03:40

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

Internet Services

Web Development

Digital Marketing

Consumer Tech