TRIM功能/使用VBA从单元格中移除空格 [英] TRIM function/remove spaces from cells using VBA

问题描述

我使用下面的代码来修剪一些包含空格的空白单元。
事情是花费太多时间，循环到每个单元格。
我想要的是删除所有单元格中的空格（开头和结尾，而不是中间的）。

I am using the code below to trim some "blank cells" that contain a space. The thing is it takes too much time, as is looping to every cell. What I want is to remove the spaces(the ones in the beginning and end, not middle), of all the cells.

有没有更简单的方法我可以一次性全部申请

Is there any easier way that I can apply all at once?

For a = 1 To ScenarioTableLastRow
    For f = 1 To ScenarioTableLastColumn

       If Cells(a, f) <> "" Then
            Cells(a, f) = Excel.Application.Trim(Cells(a, f))
       End If

    Next f
Next a

推荐答案

您将获得更好的性能，将数据复制到数组，并在数组上工作，然后将数据放回到范围内。

You'll get much better performance copying the data into an array, and working on the array, then placing the data back into the range.

另外，不要使用 Excel.Application.Trim 。这是Excel 95语法，以及具有意外错误处理的后期调用。 VBA内置了一个 Trim 函数，它的速度提高了10倍，它提供了Intellisense。

Also, don't use Excel.Application.Trim. That's Excel 95 syntax, and a late-bound call with unexpected error handling. VBA has a Trim function built-in - it's about 10 times faster and it provides Intellisense.

Sub test()

    'Assuming ScenarioTable is a range
    Dim ScenarioTable As Range
    Set ScenarioTable = Range("ScenarioTable")

    'I assume your range might have some formulas, so...
    'Get the formulas into an array
    Dim v As Variant
    v = ScenarioTable.Formula

    Dim a As Long
    Dim f As Long
    'Then loop over the array
    For a = LBound(v, 1) To UBound(v, 1)
        For f = LBound(v, 2) To UBound(v, 2)
            If Not IsEmpty(v(a, f)) Then
                v(a, f) = VBA.Trim(v(a, f))
            End If
        Next f
    Next a
    'Insert the results
    ScenarioTable.Formula = v
End Sub

这篇关于TRIM功能/使用VBA从单元格中移除空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！