trim down, copy down problems

slay0r

Board Regular
Joined
Jul 8, 2011
Messages
231
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.


Code:
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

mancubus

Active Member
Joined
Mar 7, 2010
Messages
342
if you want to trim all cells in columns D and E

Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
Try

Code:
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
 
Last edited:

slay0r

Board Regular
Joined
Jul 8, 2011
Messages
231
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jul 8, 2011
Messages
231
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

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? :)
 
Last edited:

slay0r

Board Regular
Joined
Jul 8, 2011
Messages
231
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,979
Office Version
  1. 365
Platform
  1. Windows
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 :unsure:
 

slay0r

Board Regular
Joined
Jul 8, 2011
Messages
231
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! :eek:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top