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

in the first code these lines such be the troublemakers

arrforline() = Split(content, vbLf)

For rowcount = LBound(arrfordata) To UBound(arrfordata)  'differnet name here, array isn´t filled
  Sheets("Text file").Cells(rowcount + 1, colcount + 1) = arrfordata(colcount)  'colcount isn´t filled
Next colcount 'no For colcount...
End If 'no starting If-Clause
Next rowcount

Maybe try this code instead

Sub importtxt_210512()

Dim txtfile As Integer
Dim filename As String
Dim arrforline As Variant
Dim rowCount As Long
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(arrforline) To UBound(arrforline)
  Sheets("Text file").Cells(rowCount + 1, 1) = arrforline(rowCount)
Next rowCount
End Sub

Ciao,
Holger

 

[Alex Blakenburg]

In terms of the AutoFilter part.
Your filter seems to be working for me.
If you put a breakpoint after the Autofilter line, does it look right ?
If that looks right then:
• Are you taking into account that your select range is only starting from Row 9 ?
If you have blanks not being picked up by the autofilter can you identify one of those rows and apply these formulas to Column A of that row.
eg if Row 10
=Len(A10) and
=Code(A10)

 

[ChanL]

Hi there, thanks for reply. I tried this code, and it can import the text file. But it still can't solve my second problem which is the autofilter part. It still can detect the blanks row and delete it. But, if I manually auto filter instead of using VBA, it works. So, I guess maybe there is something wrong with the code. But I not sure what is the problem.

Hi ChanL

in the first code these lines such be the troublemakers

arrforline() = Split(content, vbLf)

For rowcount = LBound(arrfordata) To UBound(arrfordata)  'differnet name here, array isn´t filled
  Sheets("Text file").Cells(rowcount + 1, colcount + 1) = arrfordata(colcount)  'colcount isn´t filled
Next colcount 'no For colcount...
End If 'no starting If-Clause
Next rowcount

Maybe try this code instead

Sub importtxt_210512()

Dim txtfile As Integer
Dim filename As String
Dim arrforline As Variant
Dim rowCount As Long
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(arrforline) To UBound(arrforline)
  Sheets("Text file").Cells(rowCount + 1, 1) = arrforline(rowCount)
Next rowCount
End Sub

Ciao,
Holger

 

[ChanL]

In terms of the AutoFilter part.
Your filter seems to be working for me.
If you put a breakpoint after the Autofilter line, does it look right ?
If that looks right then:
• Are you taking into account that your select range is only starting from Row 9 ?
If you have blanks not being picked up by the autofilter can you identify one of those rows and apply these formulas to Column A of that row.
eg if Row 10
=Len(A10) and
=Code(A10)

Hi there, I tried to fix my first code which is the import text file part and yes it works. But still the autofilter part doesn't works well, it still can't detect all the blanks row. FYI, the reason I choose row 9 is because the range (after auto filter) that I want to delete is start from row 9. If possible can you advice which part of the codes gone wrong?

 

[HaHoBe]

Hi ChanL,

wouldn´t some data from your side (maybe a sample text file or sample workbook) make it easier to track down where the error might be? Do you want to to delete all rows starting at row 9 or just those autofiltered? Your code should delete all rows starting at row 9.

Maybe this small alteration may help:

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

Ciao,
Holger

 

[HaHoBe]

Hi ChanL,

for deleting the autofiltered rows from row 9 on you may try this:

With Sheets("Text File")
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  If lastrow >= 9 Then
    On Error Resume Next
    Set rng = .Range(.Cells(9, "A"), .Cells(lastrow, "A")).SpecialCells(xlCellTypeVisible)
    If Not rng Is Nothing Then
      rng.EntireRow.Delete
      Set rng = Nothing
    End If
    On Error GoTo 0
  End If
End With

Ciao,
Holger

 

[Alex Blakenburg]

Thanks for the clarification regarding only deleting from Row 9

However regarding -

it still can't detect all the blanks row

Did you try to do the Len and Code checks ?
Since it is picking up blanks for me, it is worth checking if the blanks are really blanks or whether they are some other non-printing character which present as a blank.
Note: Code will return #Value! for an empty cell

If you have blanks not being picked up by the autofilter can you identify one of those rows and apply these formulas to Column A of that row.
eg if Row 10
=Len(A10) and
=Code(A10)

 

[ChanL]

Hi ChanL,

wouldn´t some data from your side (maybe a sample text file or sample workbook) make it easier to track down where the error might be? Do you want to to delete all rows starting at row 9 or just those autofiltered? Your code should delete all rows starting at row 9.

Maybe this small alteration may help:

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

Ciao,
Holger

Hi, I want to delete all those autofiltered starting from row 9.

 

[ChanL]

Thanks for the clarification regarding only deleting from Row 9

However regarding -


Did you try to do the Len and Code checks ?
Since it is picking up blanks for me, it is worth checking if the blanks are really blanks or whether they are some other non-printing character which present as a blank.
Note: Code will return #Value! for an empty cell

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