怎么用VBA批量去除工作簿中的前后空格？（保留公式）

getheway

请教，如何用VBA更高效地将工作表中所有单元格的前后空格去除？（同时保留公式）
——直接Excel中手动操作，我一般是在A1单元格随便加一个空格，然后选中全表，就可以批量点错误提示的“清空前后空字符串”，但是VBA好像没有对应这样的操作？
所以我尝试了第一种办法：检查所有单元格，对其中不含公式的全部使用Trim函数去除前后空格，但是这样有个问题，效率非常低，处理数据稍微多一点的就非常非常卡，代码如下：

1. Sub 清空工作簿前后空字符串()
   
2.     Dim Rng As Range
   
3.     For Each Sheet In ActiveWorkbook.Worksheets
   
4.         On Error Resume Next
   
5.         For Each Rng In Sheet.UsedRange
   
6.                 If Rng.HasFormula = 0 Then
   
7.                         Rng = VBA.Trim(Rng)
   
8.                 End If
   
9.         Next

复制代码

然后我在站内看到有类似的方法：将区域装入数组，循环数组后再写回区域，希望能够提高效率。但是数据装入数组，“hasformula”无法对数组元素判断？代码如下：

1. Sub 数组()
   
2.         ARR = ActiveSheet.UsedRange
   
3.         ReDim BRR(1 To UBound(ARR), 1 To UBound(ARR, 2))
   
4.         For I = 1 To UBound(ARR)
   
5.                 For J = 1 To UBound(ARR, 2)
   
6.                         If ARR(I, J).HasFormula = 0 Then                  <font color="#ff0000">'报错（要求对象）</font>
   
7.                                 BRR(I, J) = Trim(ARR(I, J))
   
8.                         End If
   
9.                 Next
   
10.         Next
    
11.         ActiveSheet.UsedRange = BRR
    
12. End Sub

复制代码

求问这段代码应该怎么写好呢

taller

getheway

有老师帮忙解答下吗

洋务德雷

查找和替换不能解决吗？

getheway

洋务德雷 发表于 2023-3-26 22:03
查找和替换不能解决吗？

查找和替换是去掉所有空格吧，我想要清除前后空格，中间空格是不需要清除的。
而且查找和替换是不是直接excel里手动操作的，手动操作的话我主贴里有一种方法平时自己这么操作的，就是左上角A1单元格留个空格，全选当前工作表就可以批量去除了，方法倒是有的，但是工作表sheet一多就比较麻烦……

getheway

getheway 发表于 2023-3-26 21:04
有老师帮忙解答下吗

新人求助求助

getheway

有没有大佬分享一下呀

tanglf188

用这个代码查一下，是否包含不可见字符，主要是回车换行符

1. Sub QueryInvisibleChar()
   
2.     For i = 1 To 31
   
3.         If InStr(Sheet1.[D8], Chr(i)) > 0 Then msg = msg & Chr(10) & i '以D8单元格为例
   
4.     Next
   
5.     If Len(msg) > 0 Then
   
6.         MsgBox "包含不可见字符编码如下：" & msg
   
7.     Else
   
8.         MsgBox "不包含不可见字符！"
   
9.     End If
   
10. End Sub

复制代码

getheway

taller 发表于 2023-4-3 10:32

感谢大佬 解决了我的问题，对数组完全不懂哈，学到了。

但是我发现还有个新问题~就是对于超长字符（包括但不限于），trim以后，数字文本会以科学计数法显示，但是这个问题直接用我最原始的excel界面内鼠标操作就不会遇到，请问有什么解决办法吗？因为我的目的只是批量去除各种表格的多余空格，不希望影响其他信息的存储形式哈。

taller