Import text file VBA not working

[ChanL]

Hi, I have a task which I need to write a VBA code to import text file to a sheet name "Text File". Then, I need to perform some autofilter and text to column to format the copied data into the format I wanted. The problem is everytime after I imported my text file and perform the next step (which is the autofilter and text to column), the autofilter VBA seems cannot detect the blanks row and hence I can't delete the blanks row. Below is my code :

Import text file :

Sub importtxt()

Dim txtfile As Integer
Dim filename As String
Dim arrforline() As String
Dim arrfordata() As String
Dim rowcount As Long
Dim colcount As Integer
Dim content As String
Dim delimiter As String

filename = Sheets("Main").Cells(2, 2).Value + "\" + "filename.txt"
delimiter = vbTab
txtfile = FreeFile
Open filename For Input As txtfile
content = Input(LOF(txtfile), txtfile)
Close txtfile
arrforline() = Split(content, vbLf)

For rowcount = LBound(arrfordata) To UBound(arrfordata)
Sheets("Text file").Cells(rowcount + 1, colcount + 1) = arrfordata(colcount)
Next colcount
End If
Next rowcount
End Sub

autofilter and text to column code:

Sub preparetxt()

Sheets("Text File").Columns(1).Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
    Fieldinfo:=Array(Array(0, 1), Array(5, 1), Array(23, 1), Array(42, 1), Array(54, 1), _
    Array(69, 1), Array(79, 1), Array(97, 1), Array(109, 1), Array(123, 1)), _
    trailingminusnumbers:=True
    
Application.CutCopyMode = False

'autofilter
Sheets("Text File").Columns(1).AutoFilter field:=1, criteria:=Array("CODE", "DATE", "="), _
Operator:=xlFilterValues

'delete rows
Dim lastrow As Long, rng As Range

With Sheets("Text File")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rng = .Range(.Cells(9, "A"), .Cells(lastrow, "A")) 'delete rows from A9 to the last row of the autofiltered data
rng.Select
rng.EntireRow.Delete

End With

Selection.AutoFilter

End Sub
    

End Sub

 

[HaHoBe]

Hi ChanL,

what aboiut the code sniplet from #7? Any (and if what) problems with that?

Ciao,
Holger

 

[Alex Blakenburg]

Hi, regarding the Len and Code checks, do i include it in my autofiltering VBA code or ?

Neither, we are trying to determine why Autofiltering is not picking up some of the rows you consider to be blank.

Stop the code after the filter has been applied and look at the filtered data then take the filter off and pick one of the rows that did not appear but you consider to be blank. Then in any spare cell apply =Len and = Code to column A of that row and come back to us on what they return.
If they are truly blank Len should = 0 and Code should error out.

Stop the code after this:

'autofilter
Sheets("Text File").Columns(1).AutoFilter field:=1, criteria:=Array("CODE", "DATE", "="), _
Operator:=xlFilterValues