Hello Leith Ross,
this is the file
and this is the code
Dim txtFileName As Variant
Dim destCell As Range
Dim qt
Set destCell = Worksheets("TestingImport").Cells(Rows.Count, "B").End(xlUp).Offset(1)
If destCell.Row < 9 Then Set destCell = Worksheets("TestingImport").Range("B9")
txtFileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
If txtFileName = False Then Exit Sub
Set qt = destCell.Parent.QueryTables.Add(Connection:="TEXT;" & txtFileName, Destination:=destCell.Cells(1, 1))
With qt
.TextFileStartRow = 2 'this leaves the header away
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = False 'you might not need this line at all - it might even be better to make it False
.TextFileOtherDelimiter = Empty
.TextFileSemicolonDelimiter = True
.RefreshStyle = xlOverwriteCells '<<changed/added
.Refresh BackgroundQuery:=False
With Intersect(.ResultRange.EntireRow, .Parent.Range("S:S"))
.ClearContents '<<add
'.Cells(1) = 1
.Cells(1) = .Cells(1).Offset(-1).Value + 1
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
End With
With Intersect(.ResultRange.EntireRow, .Parent.Range("A:A"))
.ClearContents '<<add
'.Cells(1) = 1
.Cells(1) = .Cells(1).Offset(-1).Value + 1
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
End With
' Intersect(.ResultRange.EntireRow, .Parent.Range("Q:Q")).Value = "x"
Intersect(.ResultRange.EntireRow, .Parent.Range("Q:Q")).SpecialCells(xlCellTypeBlanks).Value = "x"
lr = .ResultRange.Rows(.ResultRange.Rows.Count).Row
.WorkbookConnection.Delete
.Delete
End With
With Worksheets("TestingImport").Range("B9").ListObject
Set TL = .Range.Cells(1)
lc = TL.Column + .ListColumns.Count - 1
.Resize Range(TL, .Parent.Cells(lr, lc))
'delete all blank rows in the table:
For i = .ListRows.Count To 1 Step -1
With .ListRows(i)
If Application.CountA(.Range) = 0 Then .Delete
End With
Next i
End With
Thank you