Delete Excel column and rows with VBA

Question

I have a text file, I imported it into Excel thanks to VBA now I would like to remove the first column and the first 9 rows. You think it's possible to do that by rewriting the following code

      With ActiveSheet.QueryTables.Add(Connection:= _
     "TEXT;" & fpath & "\" & ffilename, Destination:=Range("$A$1"))
     .Name = "text"
     .FieldNames = True
     .RowNumbers = False
     .FillAdjacentFormulas = False
     .PreserveFormatting = True
     .RefreshOnFileOpen = False
     .RefreshStyle = xlInsertDeleteCells
     .SavePassword = False
     .SaveData = True
     .AdjustColumnWidth = True
     .RefreshPeriod = 0
     .TextFilePromptOnRefresh = False
     .TextFilePlatform = 850
     .TextFileStartRow = 1
     .TextFileParseType = xlDelimited
     .TextFileTextQualifier = xlTextQualifierDoubleQuote
     .TextFileConsecutiveDelimiter = False
     .TextFileTabDelimiter = True
     .TextFileSemicolonDelimiter = False
     .TextFileCommaDelimiter = False
     .TextFileSpaceDelimiter = False
     .TextFileOtherDelimiter = "|"
     .TextFileColumnDataTypes = Array(1)
     .TextFileTrailingMinusNumbers = True
     .Refresh BackgroundQuery:=False
 End With

FAB · Accepted Answer · 2019-06-06 09:25:43Z

2

You can add after that statement something to delete the ranges you want

With ActiveSheet
    .Columns(1).EntireColumn.Delete 'delete first column
    .Rows("1:9").EntireRow.Delete 'delete first 9 rows
End With

answered Jun 6, 2019 at 9:25

FAB

2,5791 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user11509084 Over a year ago

You can also limit where the data starts at import: .TextFileStartRow = 10

Collectives™ on Stack Overflow

Delete Excel column and rows with VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related