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
CheckCriteria routine
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)
Text import with Criteria ranges
Newton Excel Bach, not (just) an Excel Blog 
This is nice. Now when you write this, is this just for this site, for this demo, or was there another use for it? Do you put these out there for Do you put these out there for use elsewhere? This took several hours, right?
Christopher
LikeLike
Most of what I post is things I have done for my own work, education or entertainment. This has the dual benefit of forcing me to complete things to a stage where they are reasonably presentable, and to document and store them in a place where they will get indexed, so I can find them in the future. In this case it was in response to an e-mailed request for help. It was something that I could see being useful to me in the future, so I’m happy to spend a few hours on that sort of thing (if I have a few hours to spare).
LikeLike
Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog
Question received by e-mail:
“I am new to VBA was trying to write code similar to your which I found following a google search, I have tried running your code but the debugger gives a run time 1004 as I am not specifing the range in DestRange = Range(“destrange”).Value2
Can you please explain with an example how in Text In the destrange is specified.”
The easiest way to use the code in Text-in2.xls is to modify the original workbook, but if you want to start from scratch with a new file you will need to create the range names used in the code. In the case of “destrange” you will need a single cell with the name destrange, and another range (where you want the ouput to go) with the name entered in destrange (TextFileOut was used in my example, but it could be any valid name).
LikeLike
dougaj4
Thanks for the prompt reply….I’ll see what happens after work, did not realize you were using names.
Nick
LikeLike
Hello Genius.. really impressed with ur work. One small doubt.. Im a nowise in VBA.. could you please tell me wat to change If I have to just apply a direct criteria..? More Like anything that matches the word “Academy”
LikeLike
The criteria have to be numerical at the moment, but it would be possible to amend it to search for text, either in specified columns, or any column. I’ll try and find some time to do that (or if anyone else can recommend a free download that does it already, please let us know).
LikeLike
Pingback: Importing Text Files; Unix Format | Newton Excel Bach, not (just) an Excel Blog