修剪工作簿(VBA)中的所有单元格 [英] Trim all cells within a workbook(VBA)

问题描述

我一直在尝试向excel外接程序添加功能，该功能可以在用过的单元格的末尾剪裁前导空格，甚至可以解析文本，我这样做的原因仅仅是让它变成进入我已经可以使用的超链接，但是可以正常工作.

I have attempted to add functionality to an excel add-in ave been developing which trims the leading spaces at the end of used cells, and maybe even parse the text, The reason I need to do this is simply to have it turn into a hyperlink which I have already working but that parts fine.

到目前为止，这是我尝试过的操作，可以修剪active.worksheet，这很好，但是我不知道该怎么做:

This is what I have attempted so far, I have it trimming the active.worksheet am on which is fine but I can't figure out how to:

1. 修剪整个工作簿中使用的每个单元格.
2. 并在可能的情况下解析文本

这是我整理整本工作簿的尝试，我只知道一些简单的事情，但我无法弄清楚:

This is my attempt at Trimming the entire workbook, Its something simple I just know it, I just cant figure it out:

Sub DoTrim(Wb As Workbook)
Dim cell As Range
Dim str As String
Dim nAscii As Integer
Dim wsh As Worksheet

For Each wsh In Worksheets
    With wsh.UsedRange
        For Each cell In ActiveSheet.UsedRange
            str = Trim(cell)
             If Len(str) > 0 Then
                        nAscii = Asc(Left(str, 1))
                        If nAscii < 33 Or nAscii = 160 Then
                            If Len(str) > 1 Then
                              str = Right(str, Len(str) - 1)
                            Else
                                str = ""
                            End If
                        End If
                    End If
                    cell = str
        Next cell
    End With
Next wsh
End Sub

任何欢迎的建议都是该语言的新手，如果我听起来像一个完整的Newb，请对不起！

Any advice would be welcome am fairly new to this Language so sorry if I sound like a complete Newb!

TL; DR Trims单元仅在工作表上，需要在整个工作簿上运行，我无法弄清楚如何在整个事情上进行迭代.

TL;DR Trims cells only worksheet am on, needs to run across whole workbook I cant figure out how to iterate it across the whole thing.

这也是修剪这些单元格的一种更快的方法，为其设计的电子表格非常庞大，有时需要花费一些时间来修剪单元格

Is that also a quicker way of trimming these cells, the spreadsheets that are created for whom am designing this are massive and takes a while to trim the cells at times

推荐答案

未经测试

这是您要尝试的吗?

Sub DoTrim(Wb As Workbook)
    Dim aCell As Range
    Dim wsh As Worksheet

    '~~> If you are using it in an Add-In, it is advisable 
    '~~> to keep the user posted :)
    Application.StatusBar = "Processing Worksheets... Please do not disturb..."
    DoEvents

    Application.ScreenUpdating = False

    For Each wsh In Wb.Worksheets
        With wsh
            Application.StatusBar = "Processing Worksheet " & _
                                    .Name & ". Please do not disturb..."
            DoEvents

            For Each aCell In .UsedRange
                If Not aCell.Value = "" And aCell.HasFormula = False Then
                    With aCell
                        .Value = Replace(.Value, Chr(160), "")
                        .Value = Application.WorksheetFunction.Clean(.Value)
                        .Value = Trim(.Value)
                    End With
                End If
            Next aCell
        End With
    Next wsh

    Application.ScreenUpdating = True
    Application.StatusBar = "Done"
End Sub

这篇关于修剪工作簿(VBA)中的所有单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！