how to copy a table of numbers to another table, but select for criteria?

**mlevin77** · 01-27-2012, 05:00 AM

I have 2 columns of numbers to be compared by the chi squared test function in Excel. This function only works correctly if all of the numbers are > 5. So I'd like to remove any rows in which one of the numbers is < 5. Filtering doesn't do it - it removes those cells from my view, but the chi-squared function still gets them. So I think I need to somehow have it copy the numbers into another location on the sheet but omitting any rows according to my criteria. Is there any automated way to do that?

**dilipandey** · 01-27-2012, 05:57 AM

Hi Mlevin,

So basically you need some automation which will remove the row containing number < 5.

Use following code to achieve this :-

Sub chk()

Selection.CurrentRegion.Select
Selection.Range("a1").Select
    While ActiveCell.Value <> ""
        If ActiveCell.Value < 5 Or ActiveCell.Offset(0, 1).Value < 5 Then
        Selection.EntireRow.Delete
        Else
        ActiveCell.Offset(1, 0).Select
        End If

    Wend
End Sub

Regards,
DILIPandey

<click on below 'star' if this helps>

**mlevin77** · 01-27-2012, 07:07 AM

sorry if this is a stupid question, but where does one enter this code and how does one activate it later?

**mlevin77** · 01-27-2012, 07:08 AM

Actually, if the best way is with custom code, maybe the solution is to make a chi-squared routine that just ignores such values automatically?

**dilipandey** · 01-27-2012, 07:11 AM

Hi melvin,

From Excel sheet, press Alt + F11, Insert - Module, Paste this code in right side window.
Now select your data and press Alt + F8 and run this code which must be showing there by the name of chk. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>