Store data into an array if/based on specific conditions met

Hello all,

From this file, I would like to put the TradeIdentifier (i.e. 51373) in an array if the TradeVolume is less than 10, and AccountType = Pro.

This will be used to filter the data in deleting all else that does not have the same TradeIdentifier #.

The following code is what I so far had managed to come up with, and as I am very new to excel vba, I would greatly appreciate your help.


test1.xls


Sub Test()

Dim accType_col, tradeVol_col, tradeID_col As Integer
Dim acctSort() As String
Dim lngPosition As Long

accType_col = Range("N1").Column
tradeVol_col = Range("K1").Column
tradeID_col = Range("Q1").Column



'find ones with trade volume <10, and that does not have PRO and store it in array

With Range("A2")
Range(.Offset(1, 0), .End(xlDown)).Name = "dataRange"
total = Range("dataRange").Rows.Count
End With

i = 3

ReDim acctSort(1 To 1) As Single
If .Cells(i, tradeVol_col) < "10" And .Cells(i, accType_col) = "Pro" Then
acctSort(UBound(acctSort)) = .Cells(i, tradeID_col).Value
ReDim Preserve acctSort(1 To UBound(acctSort) + 1) As String
End If

With Sheets("Import")
For i = 3 To total

'filter the trades that does not have PRO and delete as whole
For lngPosition = LBound(acctSort) To UBound(acctSort)
Selection.AutoFilter Field:=17, Criteria1:="=" & lngPosition, Operator:=xlAnd
With Range("A1")
Range(.Offset(2, 0), .Offset(2, 19)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=17
End With
Next lngPosition

End Sub

Please ignore the code if it does not make sense, it does not work at the moment.