I would like a small piece of code that does the following:
Everytime the workbook is opened the text in worksheet SPtemplate2, range A1:E3000 is trimmed.
Could you help me with this please?
Thanks in advance
go into the VBE (Alt+V11). In the project explorer find the "Thisworkbook" entry for the project you are working on. Right click on that and select view code.
paste in code like this:
Private Sub Workbook_Open()
Dim r As Range, v As Variant, sh As Worksheet
Dim i As Long, j As Long
Set sh = ThisWorkbook.Worksheets("SPtemplate2")
Set r = sh.Range("A1:E3000")
v = r.Value
For i = 1 To UBound(v, 1)
For j = 1 To UBound(v, 2)
v(i, j) = Application.Trim(v(i, j))
r.Value = v
I would expect that to do what you describe. All these entries should be constants - not formulas (unless you don't want to keep the formulas).
Note that Trim function won't removed non-breaking space characters and probably some others. So if you don't get the desired results I might need to examine that sheet to see what is causing your concern.
the Workbook_Open event will fire whenever a workbook is opened as long as macros are enabled.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here