trim down, copy down problems

[slay0r]

Basically I'm trying to add a trim function into a massive report. I know what I want to trim but I'm struggling to get it to work properly with limited knowledge. (I am going on courses for it as soon as they're available!)

My copy down didn't work very well. Is there a function I can change it to so the code isn't 5000 pages long? The data size will change each time so I need to incorporate that instead of having a set range, the columns will always be set in stone though.

Sub changealphaclient()
'
' changealphaclient Macro
' Macro recorded 24/11/2011 by jmartin
'
'
    Range("AT2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-42])"
    Range("AT2").Select
    Selection.AutoFill Destination:=Range("AT2:AT8164"), Type:=xlFillDefault
    Range("AT2:AT8164").Select
    Columns("AT:AT").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=-60
    ActiveWindow.LargeScroll Down:=1
    ActiveWindow.ScrollRow = 6031
    ActiveWindow.ScrollRow = 6008
    ActiveWindow.ScrollRow = 5984
    ActiveWindow.ScrollRow = 5949
    ActiveWindow.ScrollRow = 5879
    ActiveWindow.ScrollRow = 5821
    ActiveWindow.ScrollRow = 5739
    ActiveWindow.ScrollRow = 5645
    ActiveWindow.ScrollRow = 5552
    ActiveWindow.ScrollRow = 4453
    ActiveWindow.ScrollRow = 4313
    ActiveWindow.ScrollRow = 4173
    ActiveWindow.ScrollRow = 4021
    ActiveWindow.ScrollRow = 3881
    ActiveWindow.ScrollRow = 3203
    ActiveWindow.ScrollRow = 3051
    ActiveWindow.ScrollRow = 2899
    ActiveWindow.ScrollRow = 2759
    ActiveWindow.ScrollRow = 2595
    ActiveWindow.ScrollRow = 2443
    ActiveWindow.ScrollRow = 2303
    ActiveWindow.ScrollRow = 2151
    ActiveWindow.ScrollRow = 2011
    ActiveWindow.ScrollRow = 1882
    ActiveWindow.ScrollRow = 1742
    ActiveWindow.ScrollRow = 1637
    ActiveWindow.ScrollRow = 1485
    ActiveWindow.ScrollRow = 1357
    ActiveWindow.ScrollRow = 1216
    ActiveWindow.ScrollRow = 1088
    ActiveWindow.ScrollRow = 948
    ActiveWindow.ScrollRow = 842
    ActiveWindow.ScrollRow = 749
    ActiveWindow.ScrollRow = 667
    ActiveWindow.ScrollRow = 609
    ActiveWindow.ScrollRow = 539
    ActiveWindow.ScrollRow = 504
    ActiveWindow.ScrollRow = 457
    ActiveWindow.ScrollRow = 422
    ActiveWindow.ScrollRow = 387
    ActiveWindow.ScrollRow = 352
    ActiveWindow.ScrollRow = 340
    ActiveWindow.ScrollRow = 305
    ActiveWindow.ScrollRow = 281
    ActiveWindow.ScrollRow = 258
    ActiveWindow.ScrollRow = 235
    ActiveWindow.ScrollRow = 200
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 1
    Range("AU2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-42])"
    Range("AU2").Select
    Selection.AutoFill Destination:=Range("AU2:AU8164")
    Range("AU2:AU8164").Select
    Columns("AT:AU").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("D:D").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Alphaname"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Longname"
    Range("E2").Select
End Sub

 

[mancubus]

if you want to trim all cells in columns D and E

Sub TrimCells()
 
Dim cll As Range
Dim LastRow As Long
 
LastRow = Range("D" & Rows.Count).End(xlUp).Row
 
For Each cll In Range("D2:E" & LastRow)
    If Len(cll) > Len(Trim(cll)) Then
        cll.Value = Trim(cll.Value)
    End If
Next cll
 
End Sub

 

[jasonb75]

Try

Sub changealphaclient()
Dim mCell As Range
   For Each mCell In [D:E].SpecialCells(xlConstants, xlTextValues)
      mCell.Value = Trim(mCell.Value)
   Next
[D1] = "Alphaname"
[E1] = "Longname"
[D:E].EntireColumn.AutoFit
End Sub

 

[slay0r]

Sweet, do I need to add in a worksheet reference? This is part of a gigantic process that's running and this is just a tiny little fix that will fix a LOT of other errors (The spaces are messing up Vlookups and all sorts in other reports that link to it)

If so where would I put that? Can you put like an "insert name here" for it if possible?

Really appreciate the help and the replies guys, feel I'm getting somewhere at the moment with all of the help.

 

[jasonb75]

By default the code will work with the active (visible) sheet, but can be defined to other sheets if you need.

Alternatively, if you need it to run on every sheet in a workbook, that can be done too.

 

[slay0r]

The process involved is that it's opening a lot of spreadsheets copying data/vlookups then closing them etc, I'm not sure which ones would be open at a specific time.

ActiveSheet.Name = "AR Report"

Would this fix it if I defined which report it was that I wanted to change and added it in?

 

[jasonb75]

Just to clarify, do you need the code to run on each workbook as you open it, or on data that you copy to the current workbook? This would run the code on the "AR Report" sheet, but only in the workbook that holds the code.

Public Sub changealphaclient()
    Dim mCell As Range
With Sheets("AR Report")
    For Each mCell In .[D:E].SpecialCells(xlConstants, xlTextValues)
        mCell.Value = Trim(mCell.Value)
    Next
    .[D1] = "Alphaname"
    .[E1] = "Longname"
    .[D:E].EntireColumn.AutoFit
End With
End Sub

It is possible to run the code on all open workbooks if that is what you need, but I'll have to look into that if it's what you need, unless mancubus has it committed to memory?

 

[slay0r]

It will only be on the 1 sheet that I need changing, the rest of them all have different data that isn't trimming. Unfortunately the system that's feeding the data adds spaces onto any names that are less than 10 characters and I need these removing for my vlookups in other reports to function correctly. It's only on the AR Report sheet that I need it to change, does that make sense?

 

[jasonb75]

I think I get this now, is there a sheet called "AR report" in each of the workbooks?

Sorry if I'm missing something obvious that you're already explained, think I need recaffeinating

 

[slay0r]

There should be an AR report in every workbook, not checked it out entirely yet (you should see the size of this thing, whoever created it must have been a bit of a god)

At this point I'm adding it in it's calling data from another report and adding it to "AR Report" So that's what made me think I need to define AR Report as the sheet it wants to trim.

Am I right in thinking that? You're right about the caffine..these things shouldn't be cropping up at 4pm!