Trim Blanks

[Todd Huttenstine]

Hey guys I have an Excel assistant that has a function to
trim blanks. I use this Trim Blanks function allot as I
am working with data from querys. THe problem is that I
have to do it manually. If I record a macro and then use
the Trim Blanks function, the macro does not capture the
code. For this reason I have to do it manually. Is there
a code that will do this?


Thank you

Todd Huttenstine

 

[kkknie]

Not sure what the trim blanks function is that you are using, but ther
is function called Trim() which should do the trick. Are you workin
with the query manually or importing it through code?

If you are doing it manually, simple macro (which might take a bit t
run) would be:

Sub TrimEntireWorksheet()

Dim r As Range

For Each r In ActiveSheet.UsedRange
r.Value = Trim(r.Value)
Next

End Sub

Just run this after you do the import.

 

[Frank Kabel]

Hi Todd
have a look at
http://www.mvps.org/dmcritchie/excel/join.htm#TRIMALL

 

[pikus]

You can use "Application.Sendkeys" to effectively press the keys you'
need pressed, but I'd recomment using the Trim Function. Either:

Application.SendKeys("%T") {%=Alt here}
I've had some wierd results with this one that were cleared up after
added "Application.Wait(1)" after it. Maybe that was just me...

Or:

Cells(1, 1).Value = Trim(Cells(1, 1).Value)

Take your pick. - Piku

 

[Patrick Molloy]

you could just wrap the result in a trim function

It depends on how your data look...but an example:
Suppose A2:A1000 contain text from a query that has
leading and trailing spaces
for thisrow = 2 to 1000
cells(Thsisrow,"A").Value = _
TRIM(cells(thisrow,"A").Value
next

HTH
Patrick Molloy
Microsoft Excel MVP

 

[David McRitchie]

Hi Todd,
Go with Frank Kabel's reply pointing to my TRIMALL macro in
http://www.mvps.org/dmcritchie/excel/excel.htm

The other solutions that were provided in this thread would wipe
out formulas within your selection or within the hard coded
ranges. Something you might not be immediately aware of.
One of the advantages of a webpage is that a more
complete solution can be provided.

When you record a macro you have to change to generated
code in almost all cases to get it to work for you in a generic
fashion such as on a selection. Recoding a macro will tell you
what instructions you might use.