Import text file VBA not working

ChanL

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
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 :
VBA Code:
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:
VBA 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

Active Member
Joined
Jan 24, 2003
Messages
474
Office Version
  1. 2013
Platform
  1. Windows
Hi ChanL,

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

Ciao,
Holger
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,147
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
'autofilter
Sheets("Text File").Columns(1).AutoFilter field:=1, criteria:=Array("CODE", "DATE", "="), _
Operator:=xlFilterValues
 

Forum statistics

Threads
1,136,735
Messages
5,677,462
Members
419,693
Latest member
divtjd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top