Skipping rows while importing a text file if a field contains 0

**fgingell** · 02-21-2011, 11:50 AM

I am importing a tab delimited text file and need to skip rows, that is not to import row, whose 4th field (column) is equal to zero. My current code is:

Sub aged()
'
' aged Macro

    Dim strinp As String
    strinp = Environ("userprofile") & "\My Documents\Dimensions Reports\Customer List.txt"

    Sheets("ImpCust").Select
    ActiveSheet.Unprotect
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strinp _
        , Destination:=Range("A1"))
        .Name = "Customer List"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
        , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
        , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 1, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
' I need coding here to test the 4th field of each row and if equal to 0 will skip

    End With

End Sub

The following is a sample of the imported text, but I do not want to import the 3rd and 6th rows as their value is 0.00
34000001 INV 28-Dec-06 796.3
34000002 INV 24-Jan-07 23.29
34000002 INV 29-Jan-07 0.00
34000005 INV 31-Jan-07 139.76
34000005 PAY 31-Jan-07 796.3
34000005 INV 15-Mar-07 0.00
34000007 PAY 24-Mar-07 186.35
34000007 PAY 25-Apr-07 273.7

Would appreciate a solution. Thank you in anticipation.
34000008 INV 22-Jun-07 19.8

**Leith Ross** · 02-21-2011, 03:59 PM

Hello fgingell,

Welcome tot he Forum!

You can actually skip lines in a text file using the text import method. Rather, you have to check the data once it has been transferred onto the worksheet and delete rows accordingly. based on you r example, the macro assumes the sample shown to be in columns "A" through "D". A second macro will examine each cell in column "D" and delete the row if it has a zero value. A call is made from the first macro to second, after the data has been imported.

Amended First Macro

Sub aged()
'
' aged Macro

    Dim strinp As String
    strinp = Environ("userprofile") & "\My Documents\Dimensions Reports\Customer List.txt"

    Sheets("ImpCust").Select
    ActiveSheet.Unprotect
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strinp _
        , Destination:=Range("A1"))
        .Name = "Customer List"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
        , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
        , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 1, _
        9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
' I need coding here to test the 4th field of each row and if equal to 0 will skip

   Call DeleteZeroDollars

    End With

End Sub

Second Macro to Delete Rows

Sub DeleteZeroDollars()

  Dim R As Long
   
    Application.ScreenUpdating = False
    
    For R = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
      If Cells(R, "D") = 0 Then
         Rows(R).EntireRow.Delete
      End If
    Next R
    
    Application.ScreenUpdating = True
    
End Sub