The following code may help:-
Dim lr As Long: lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Dim lCol As Long: lCol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
Dim i As Long
Application.ScreenUpdating = False
On Error Resume Next
For i = 2 To lr
Sheet1.Range(Cells(i, 1), Cells(i, lCol)).SpecialCells(2, 2).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
On Error GoTo 0
Application.ScreenUpdating = True
The code assumes that your data is in Sheet1 and starts in row2 with headings in row1. The "repaired" data is transferred to sheet2.
I've attached a sample workbook to show you how this code works. Click on the "TEST ME" button to see how it works.
The code only takes a second to work in the sample but be prepared for it to take up to three minutes to work on about 10,000 rows of data. The time taken may vary from machine to machine.
Here is the link to the sample file:-
I hope that this helps.