如何让此VBA代码在指定位置遍历工作表并删除每个单元格中回车符?

Question

所以我需要有效地删除 CSV 中的回车符，以便输入到一个单独的软件中，该软件会被换行符绊倒。这些CSV的问题在于单元格内的换行符可能在任何地方，并且它们是数千列和行。

我找到了一些代码，可以通过遍历所有单元格并将所有回车符(字符 10 是 excel 中指定的字符(替换为空格来成功删除回车符。我曾尝试编辑活动工作簿部分以指定某个位置的工作簿/工作表，但它引发了一个错误。我提供了用于删除以下退货的代码。我现在只需要它在某个指定的工作簿中执行此操作，并且可能通过选择文件对话框而不打开所述工作簿。

Sub RemoveCarriageReturns()
Dim MyRange As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each MyRange In ActiveSheet.UsedRange
If 0 < InStr(MyRange, Chr(10)) Then
MyRange = Replace(MyRange, Chr(10), "")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Answer 1

连同BigBen的评论，这应该可以帮助您：

Option Explicit
Sub RemoveCarriageReturns()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim wb As Workbook 'declare a workbook type variable
Dim MyFile As String 'declare a string variable to hold the file you want to pick
Dim fd As FileDialog 'declare a file dialog variable
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Title = "Please select a file"
.Filters.Clear
.Filters.Add "*", "*.csv"  'filter only csv files
If .Show = True Then
MyFile = .SelectedItems(1)
Else
MsgBox "you didn't pick a file, please start over."
End
End If
End With
Set wb = Workbooks.Open(MyFile) 'set the workbook variable openning the file you picked
With wb.Sheets(1) 'assuming you have only 1 sheet per file, if not change 1 for "SheetName"
.UsedRange.Replace Chr(10), ""
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub