Hi Dave,

I found a workaround to my issue -- if I first delete out column AD before
putting in the formula into AD that puts the data into AD, the issue does not
come up.

Out of curiosity, is there a way to code so that when copying a cell value,
the code copies from the formula bar instead of the actual cell? When I
record-macro and do this, it just says "Activecell.formula = ...".

Thanks for your help!
--
Robert


"Dave Peterson" wrote:

Are you copying from the formula bar?

If no, then try that.

robs3131 wrote:

Hi Dave,

The code I wrote below does replace spaces when I actually go in and add a
space at the end of a word in a cell, however, it does not replace the
leading and lagging spaces that already exist in column AD.

I'm starting to wonder if the problem has to do with something else
altogether -- when I click into a cell in column AD, there are no leading or
lagging spaces to the value in the cell (the cursor blinks directly next to
the first and last characters when I click there) -- leading and lagging
spaces show up when I copy a cell in column AD and then bring up the "Find"
menu by clicking CTRL F and then pasting the value into the "Find What"
box...any idea on what would cause this and/or how to resolve this?

Thanks!

--
Robert

"Dave Peterson" wrote:

Make sure your code is in a General module--not behind a worksheet and not
behind ThisWorkbook.

And

Sheets("Sheet1").Select
range("AD1").entirecolumn.Select

looks like it would work to me.

But your code looked like it would have gotten rid of all the spaces on the
worksheet. What happened when you tried it?

robs3131 wrote:

Hi all,

I'm having a problem removing leading and lagging spaces from cells in a
column of data (FYI - the data was input from an Excel spreadsheet that was
dowloaded from a website). I looked through some other postings and found
the "Trimall" macro -- I've tried this but it is still not removing the
spaces. Below is the Trimall macro with two lines that I added (to select
all cells in Sheet1) noted with **. Column AD specifically has the data that
I am trying to remove the spaces from.

I appreciate your help!

Sub TrimALL()

**Sheets("Sheet1").Select
**Cells.Select
'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm
'-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
' - Optionally reenable improperly terminated Change Event macros
Application.DisplayAlerts = True
Application.EnableEvents = True 'should be part of Change Event macro
If Application.Calculation = xlCalculationManual Then
MsgBox "Calculation was OFF will be turned ON upon completion"
End If
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(21), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'---------------------------
Selection.Replace What:=Chr(8), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(9), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

--
Robert

--

Dave Peterson


--

Dave Peterson