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.
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:
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
Any advice would be welcome am fairly new to this Language so sorry if I sound like a complete Newb!
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.
EDIT: 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
Untested
Is this what you are trying?
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