I was recently asked if the routine in the Text-in2 spreadsheet to import and split data from text files could be modified to only import rows that met specified criteria. A similar end result can be achieved by importing the whole file, then using the Excel data filtering routines, but applying the selection criteria at the text import stage does have some advantages:
- It allows selection of data from very large files, without exceeding the row limit in Excel.
- The process is quicker to set up.
- The use of VBA allows the possibility of more flexible import criteria.
This feature has now been added to the spreadsheet, which can be downloaded from the link above. The routine as written checks that the values in the specified columns are within a specified numerical range, but it could easily be amended to work on text.
The download file includes full open source code, and the new routines to read and check the criteria are shown below:
Modifications to the GetData Function:
... ApplyCriteria = Range("ApplyCriteria").Value2 If ApplyCriteria(1, 1) > 0 Then Criteria = Range("CriteriaRng").Value2 NumCrit = UBound(Criteria) StartRow = ApplyCriteria(1, 1) If ApplyCriteria(1, 2) > 0 Then EndRow = ApplyCriteria(1, 2) Else NumCrit = 0 End If ... If NumCrit > 0 Then If i >= StartRow And i <= EndRow Then CheckCriteria DataA2, Criteria, NumCrit, i, m End If
Sub CheckCriteria(ByRef DataA2 As Variant, ByRef Criteria As Variant, ByVal NumCrit As Long, ByVal i As Long, ByRef m As Long) Dim n As Long, Chkcol As Long, DatVal As Double For n = 1 To NumCrit Chkcol = Criteria(n, 1) If Chkcol <> 0 Then DatVal = Val(DataA2(m, Chkcol)) If Criteria(n, 2) <> "" Then If DatVal < Criteria(n, 2) Then m = m - 1 Exit Sub End If End If If Criteria(n, 3) <> "" Then If DatVal > Criteria(n, 3) Then m = m - 1 Exit Sub End If End If End If Next n End Sub
The spreadsheet needs two additional named ranges:
- ApplyCriteria (B16:C16): the row number of the first and last line to be imported
- CriteriaRng (A19:C24): Column number, minimum and maximum values to be imported.
Input and typical output are shown in the screenshot below (click for full size view)