Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Blanks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Blanks
Hi Todd
have a look at http://www.mvps.org/dmcritchie/excel/join.htm#TRIMALL -- Regards Frank Kabel Frankfurt, Germany Todd Huttenstine wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Blanks
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. -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Blanks
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Blanks
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 -----Original Message----- 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Blanks
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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Todd Huttenstine" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blanks non blanks in filter | Excel Discussion (Misc queries) | |||
Sumproduct copying blanks or how to insert zero into blanks | Excel Worksheet Functions | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) | |||
Trim like worksheet Trim | Excel Programming |