excel - VBA，避免溢出错误，修剪工作表功能

Question

我在尝试修剪范围内的单元格时遇到溢出错误。我得到错误的那一行是 C.Value = .Trim(C.Value)也许这可以在没有相交的情况下完成？我试过没有它，但它会导致不匹配错误。

    Dim masterWB As Workbook
    Dim dailyWB As Workbook
    Dim C As Range

    Application.DisplayAlerts = False

    'Set Current Workbook as Master
    Set masterWB = Application.ThisWorkbook
    'Set some Workbook as the one you are copying from
    Set dailyWB = Workbooks.Open("excelguy.xlsm")


    'Copy the Range from dailyWB and Paste it into the MasterWB
    dailyWB.Sheets("Summary").Range("A1:BJ200").Copy masterWB.Sheets("Master Summary").Range("A1").Rows("1:1")
    'formatting and paste as values
    Workbooks("excelguy Master.xlsm").Activate
    Worksheets("Master Summary").Select

    'trim values
    columns("A:BJ").Select
       With Application.WorksheetFunction
        For Each C In Intersect(columns("A:BJ"), ActiveSheet.UsedRange)  
            C.Value = .Trim(C.Value)  'Overflow Error
        Next C
        End With

任何帮助，将不胜感激。

Answer 1

无需.Select或 .Activate您的工作簿/工作表。您声明了工作簿变量，所以 使用它们 !

UsedRange可能不可靠。我建议切换到更标准的最后一行计算。现在，代码使用 Column A确定范围内所有列的最后一行，这反过来又确定了要循环的范围。

正如@dwirony 所说，TRIM函数可以直接从VBA调用.

请参阅下面@Tim Williams 的评论以确定是否可以接受 VBA 版本的 Trim

Option Explicit

Sub Test()

Dim masterWB As Workbook, dailyWB As Workbook
Dim C As Range, LRow As Long

Set masterWB = Application.ThisWorkbook
Set dailyWB = Workbooks.Open("excelguy.xlsm")

dailyWB.Sheets("Summary").Range("A1:BJ200").Copy masterWB.Sheets("Master Summary").Range("A1").Rows("1:1")

With masterWB.Sheets("Master Summary")
  LRow = .Range("A" & .Rows.Count).End(xlUp).Row
    For Each C In .Range("A2:BJ" & LRow)
        C.Value = Trim(C)
        'C.Value = Application.WorksheetFunction.Trim(C)
    Next C
End With

End Sub

如果您只是修剪值，则可以将范围加载到数组中，将值修改为新数组，然后将新修剪后的数组的值传输到范围