+ Reply to Thread
Results 1 to 6 of 6

Excel macro to automatically remove leading apostrophe from entire column

  1. #1
    Registered User
    Join Date
    09-09-2011
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    52

    Excel macro to automatically remove leading apostrophe from entire column

    Hi,

    Sometimes we work with Excel files that have a leading apostrophe as a leading character. I'm not referring to the hidden apostrophes Excel puts in front of numbers formatted as text, but clearly visible apostrophes.

    I need a macro that can run through a column of data and strip the leading apostrophe from each cell where it exists.

    Right now, I'm using this:

    Please Login or Register  to view this content.
    This works fine for manually highlighting the column and running the macro. But I would like the macro to run automatically when the file opens, or maybe even constantly. Any suggested solutions?

    Thanks,
    hk106

  2. #2
    Valued Forum Contributor
    Join Date
    05-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    354

    Re: Excel macro to automatically remove leading apostrophe from entire column

    How about something like this? call this at startup
    Please Login or Register  to view this content.
    Regards,
    Vandan

  3. #3
    Registered User
    Join Date
    09-09-2011
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Excel macro to automatically remove leading apostrophe from entire column

    Vandan,

    Does your code search all cells in the worksheet? I would need mine to search only Column E. Also, how do you "call" a macro at startup?

    Thanks,
    hk106

  4. #4
    Valued Forum Contributor
    Join Date
    05-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    354

    Re: Excel macro to automatically remove leading apostrophe from entire column

    Yes it does. It goes through each sheet of the workbook and then each cell of the worksheet.

    following will go through column E of each worksheet.

    Please Login or Register  to view this content.
    To run the above macro at startup, go to the "This Workbook" Object and add workbook_open sub.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-09-2011
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Excel macro to automatically remove leading apostrophe from entire column

    Vandan,

    Thanks for the response. So to have this macro only update the first tab, do I just remove the outer "For Each" statements? Is the first tab the ActiveSheet by default?

    Thanks,
    hk106

  6. #6
    Valued Forum Contributor
    Join Date
    05-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    354

    Re: Excel macro to automatically remove leading apostrophe from entire column

    First tab is not the active tab. It is usually the sheet1 (or what ever you renamed it as).

    To see the sheet names/numbers, you can go to your VBA editor and look in the project explorer (usually located to the left) --> your file name --> Microsoft excel objects. Sheet numbers and their associated sheet names are listed here.

    Now to answer your question, you are right. you will need to replace the outer "with each loop"

    Please note I haven't tested the code below. Let me know if it doesn't work


    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)

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