Can you help fix this macro?

[JonnyEnglish]

Hi, I can’t find what is wrong with this macro, can you guys help? I’ve attached a couple of files to hopefully demonstrate the problem and to give you a look at what I’m after.

Any help would be appreciated

[SamT]

OP code

Sub OpenTextFileAndSave()
    Dim lngR As Long
    Dim strName As String
    
    strName = Application.GetOpenFilename("txt files,*.txt")
    If strName = "False" Then Exit Sub
    
    Workbooks.OpenText Filename:= _
        strName, Origin:=1257, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:=":", FieldInfo:=Array(Array(1, 1 _
        ), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
    With ActiveSheet.UsedRange
        .Range("B:B").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=TRIM(R[1]C)"
        .Range("C:C").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=TRIM(SUBSTITUTE(LEFT(R[1]C,FIND("")"",R[1]C)-1),""("",""""))"
        With .Range("D:D").SpecialCells(xlCellTypeBlanks)
            .FormulaR1C1 = "=VALUE(TRIM(MID(R[1]C[-1],FIND("")"",R[1]C[-1])+1,LEN(R[1]C[-1]))))"
            .Offset(0, 1).FormulaR1C1 = "=TRIM(R[1]C[-1])"
        End With
        .Range("B:E").Value = .Range("B:E").Value
    End With
    For lngR = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -2
    Cells(lngR, 1).EntireRow.Delete
    Next lngR
    Range("A:E").EntireColumn.AutoFit
    ActiveWorkbook.SaveAs Replace(strName, ".txt", ".xlsx"), FileFormat:=51
End Sub

Sample of OP data source

"Chrispy1990___________X:"
Optimized Deck: 8 units: (0.113529% stall) 69.8303: Halcyon, Stonewall Garrison, Ambush Neutralizer, Blast Twin, Spur Buggy, Station Fortifier, Hawkler Gun, Top Hawkler, Sky Flyer
"Breezus_______________X:"
Optimized Deck: 8 units: (0.0208824% stall) 86.0953: Silus the Corrupt, Zorbo the Master, Extreme Barrager, Tetrapede Comber, Orion Survivor, Atomic Wardriver #4

Sample Desired Output

Chrispy1990___________X	8 units	0.113529% stall	69.8303	Halcyon, Stonewall Garrison, Etc
Breezus_______________X	8 units	0.0208824% stall	86.0953	Silus the Corrupt, Etc

[JonnyEnglish]

Hi sorry bud. The macro is meant to import and sort data from the source file into excel so it looks like the data in the “desired output” workbook but at the moment it’s replacing people’s names with “Optimized Deck” as illustrated in the “current output” workbook

[SamT]

at the moment it’s replacing people’s names with “Optimized Deck”

You're probably deleting the wrong Rows.

See what you get when you comment out this line

For lngR = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -2 
        'Cells(lngR, 1).EntireRow.Delete 
    Next lngR

[JonnyEnglish]

End With

'For lngR = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -2
'Cells(lngR, 1).EntireRow.Delete
'Next lngR
'Range("A:E").EntireColumn.AutoFit

For i = 2 To 100
Cells(i, 1).Select
ActiveCell.EntireRow.Delete
Next i
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
ActiveWorkbook.SaveAs Replace(strName, ".txt", ".xlsx"), FileFormat:=51
End Sub

Ok so I replaced the txt in red with the code below it and it seems to work. Thanks for point me in the right direction.

[SamT]

Try this

i = Cells(Rows.Count, "A").End(xlUp).Row
If  i Mod 2 = 1 then i = i + 1 'If i is odd then add 1

For i = i To  To 2 Step -2 'Yes, you can reuse variables like this
Rows(i).Delete
Next i