VBA Question: how to delete cells from a flexible array, resulting in a smaller array

[Pascalvrolijk]

Hi dear helper!

I would like to get some help with this VBA problem i am facing:
In excel i drew 5 times (columns of) 250.000 numbers from a N(0,1) distribution and manipulated them in a certain, necessary, way.
So my basis are 5 columns (A,B,C,D,E) with 250.000 numbers, the numbers are value-copies of formulas.

From this starting point, now my problem is that i need to delete unrequired numbers (each column has a different requirement; say requirement column 1,2,3,4,5 is: value1<1;value2>1;value3>1.1;value4<0.003;value5>0.5). Once the unrequired numbers are deleted the first 100.000 numbers need to be retained and printed in the columns next to it (columns G,H,I,J,K). They should be printed without changing the order of appearance (so not sort from high to low, or low to high)

I would really appreciate any help.

Thank you so much,
Pascal.

 

[JackDanIce]

Untested, however, try:

Sub Macro1()

Dim x   As Long
Dim y   As Long
                
Dim arrOperators()  As Variant

arrOperators = Array("<1", ">1", ">1.1", "<0.003", ">0.5")
                
                
Application.ScreenUpdating = False
        
    With ActiveSheet
    
        If .AutoFilterMode Then .AutoFilterMode = False
        
        For x = 1 To 5
            y = .Cells(.rows.Count, x).End(xlUp).row
            With .Cells(1, x).Resize(y)
                .AutoFilter field:=1, Criteria1:=arrOperators(x - 1)
                .Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy
                .Cells(2, x + 6).PasteSpecial xlPasteValues
                Application.CutCopyMode = False
            End With
        Next x
        
        .Range("G1:K1").value = .Range("A1:E1").value
        .AutoFilterMode = False
        
        For x = 7 To 11
            y = .Cells(.rows.Count, x).End(xlUp).row
            If y > 100000 Then .Cells(100001, x).Resize(y - 99999).ClearContents
        Next x
    End With

    Erase arrOperators

Application.ScreenUpdating = True

End Sub

 

[Pascalvrolijk]

Hi JackDanIce,
Thank you so much for your answer, it has been most helpful. Sorry though that i couldnt react sooner. It took me quite a while but i also had to solve a few problems in your code.
Most friendly greetings,
Pascal.

 

[JackDanIce]

Yes untested and without your spreadsheet infront of me, it was best guess! Hope it's working now.

 

[Pascalvrolijk]

Hi,
Could anyone help me to change the filter from the first column to the next?
Changing .AutoFilter field:=1 into .AutoFilter field:=x gets an error for x=2..

With ActiveSheet
    
        If .AutoFilterMode Then .AutoFilterMode = False
        
        For x = 1 To 5
            y = 210000
            With .Range([startcel2]).Offset(0, x - 1).Resize(y)
                .AutoFilter field:=1, Criteria1:=arrOperators(x - 1)
                .Resize(y).SpecialCells(xlCellTypeVisible).Copy
                .Cells(1, 7).PasteSpecial xlPasteValues
                Application.CutCopyMode = False
            End With
        Next x
        
        
        .AutoFilterMode = False
        

    End With

Thank you very much,
Pascal.