如何修改Excel VBA代码拆分逗号分隔值并删除奇数列？

Got it, let's fix up your VBA code to meet your requirements—splitting those comma-separated values into adjacent columns and then removing all odd-numbered columns after processing. Here's the revised code with detailed explanations:

Sub LoopThroughTextFiles()
    ' Defines variables
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim Textline As String
    Dim Result() As String
    Dim LastCol As Long
    Dim RowCount As Long
    Dim i As Long
    Dim col As Long
    
    ' Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    ' Start at the first empty column after existing data in row 1
    LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column + 1
    
    ' Sets the folder containing the text files
    myPath = "C:\Users\ashra_ua\OneDrive - Stichting Deltares\Desktop\Excel2\"
    
    ' Target File Extension (must include wildcard "*")
    myExtension = "*.txt"
    
    ' Get first text file in the folder
    myFile = Dir(myPath & myExtension)
    
    ' Loop through each text file in folder
    Do While myFile <> ""
        RowCount = 1
        
        ' Open the text file for reading
        Open myPath & myFile For Input As #1
        
        ' Process each line in the text file
        Do Until EOF(1)
            ' Read a single line from the text file
            Line Input #1, Textline
            
            ' Write the full comma-separated line to the current column
            Cells(RowCount, LastCol).Value = Textline
            
            ' Split the line into individual values using comma as delimiter
            Result = Split(Textline, ",")
            
            ' Write each split value to adjacent columns starting from LastCol + 1
            For i = LBound(Result) To UBound(Result)
                Cells(RowCount, LastCol + 1).Offset(0, i).Value = Result(i)
            Next i
            
            ' Move to the next row for the next line of text
            RowCount = RowCount + 1
        Loop
        
        ' Close the text file
        Close #1
        
        ' Move to the next column pair (each file uses 2 columns: original + split)
        LastCol = LastCol + 2
        
        ' Get the next text file name
        myFile = Dir
    Loop
    
    ' Delete all odd-numbered columns (start from right to left to avoid index shifting issues)
    For col = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
        If col Mod 2 = 1 Then
            Columns(col).Delete
        End If
    Next col
    
ResetSettings:
    ' Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    ' Notify user when tasks are completed
    MsgBox "Task Complete!"
End Sub

Key Changes & Explanations: #

• Per-Row Splitting: The original code only split the last line of each text file—now we split every line as we read it, writing the split values directly to the adjacent column(s) for that row.
• Safe Column Deletion: We delete odd columns starting from the rightmost column and moving left. If we deleted left-to-right, deleting column 1 would shift all subsequent columns left, leading to incorrect deletions.
• Removed Unused Logic: Got rid of the Text and DisplayText variables that weren't contributing to the final output, and fixed the column increment logic to properly account for each file's two columns (original + split).
• Initial Column Setup: We start processing at the first empty column after existing data, so you won't overwrite any pre-existing content in your sheet.

内容的提问来源于stack exchange，提问作者Muhammad Usama Ashraf