+ Reply to Thread
Results 1 to 14 of 14

VBA TRIM is won't TRIM active worksheet

  1. #1
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    VBA TRIM is won't TRIM active worksheet

    Hi everyone,

    This must be the most annoying function VBA has. I just can't seem to get it to work. All I'm trying to do is create a macro to trim the active sheet:

    Please Login or Register  to view this content.
    At one point I didn't get an error but TRIM didn't activate. Now I am getting an error on various points of the code. Help!!!

    Thanks everyone!
    Last edited by Groovicles; 06-06-2014 at 09:30 AM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA TRIM is kicking my ***

    You shoud rename the sub, Trim is a VBA function.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: VBA TRIM is kicking my ***

    Hi Norie,

    Thats a great point. I have done that, but the macro still isn't trimming any of the cells in the range (not getting any errors though!). Any ideas?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA TRIM is kicking my ***

    What happens if you remove On Error Resume Next?

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA TRIM is kicking my ***

    What is in the cells that you expect to be trimmed?

    Any chance you could upload the workbook or at least the relevant sheet.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,134

    Re: VBA TRIM is kicking my ***

    You can get errors on simple functions like TRIM and LEFT, if the MS Visual Basic drivers are not in REFERENCES.

  7. #7
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: VBA TRIM is kicking my ***

    Hi Norie,

    I get Run-time error '13' Type mismatch

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA TRIM is kicking my ***

    I can't recreate the error, I tried adding errors to the range, merging some cells etc but still no error.

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: VBA TRIM is kicking my ***

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  10. #10
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├┤ Pew Pew
    Posts
    441

    Re: VBA TRIM is kicking my ***

    Ninja'd by a mod.
    Last edited by Hawkeye16; 06-06-2014 at 09:30 AM. Reason: did not see mod reply

  11. #11
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: VBA TRIM is kicking my ***

    Hi,

    Here's a small sample workbook. Thats very much for your help.
    Attached Files Attached Files

  12. #12
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: VBA TRIM is won't TRIM active worksheet

    thespace before the H is char 160 trim wont remove that
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  13. #13
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: VBA TRIM is won't TRIM active worksheet

    Hi Martin,

    Oh i see. Will vlookups still pick it up even if there is a space like that?

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA TRIM is won't TRIM active worksheet

    This will get rid of the Chr(160) non-printing characters, and trim.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Rogue #N/A appearing - kicking my butt
    By n3mcx1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-05-2014, 04:16 PM
  2. Slow loading and kicking me out
    By Pepe Le Mokko in forum Suggestions for Improvement
    Replies: 8
    Last Post: 10-05-2013, 11:55 AM
  3. Kicking someone out of excel with VBA
    By danmarson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-31-2013, 05:30 AM
  4. Formulaes kicking in when certain criteria is met
    By stephencheslett in forum Excel General
    Replies: 7
    Last Post: 09-30-2009, 10:25 AM
  5. [SOLVED] Kicking off macro if data in cell changes
    By dumb and frustrated in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-16-2006, 05:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1