+ Reply to Thread
Results 1 to 9 of 9

VBA macro to count & highlight cells with non-ASCII characters

  1. #1
    Registered User
    Join Date
    04-25-2016
    Location
    Norway
    MS-Off Ver
    2013
    Posts
    8

    VBA macro to count & highlight cells with non-ASCII characters

    i work with an excel file that has cells with text primarily in English, however some of them are in non-ASCII characters. When i try to save as CSV (comma delimited), these characters appear as question marks in the CSV file.

    So, i'm looking for a macro that checks each character in all cells of the active worksheet; if the cell contains a non-ASCII or a newline character, it changes the fill color of the cell. In the end a message mentions the total number of the highlighted cells.

    This is posted over MrExcel too, but still no full working solution. The macro by Wolf so far is the following (with dark red is my comment) and works only if the non-ASCII character is the first letter in the cell:

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: VBA macro to count & highlight cells with non-ASCII characters

    Try something like this...


    Please Login or Register  to view this content.
    Just for clarification: both the so-called non-ASCII question mark (63) and NewLine (10) characters are ASCII characters.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: VBA macro to count & highlight cells with non-ASCII characters

    Quote Originally Posted by Dimitris254 View Post
    So, i'm looking for a macro that checks each character in all cells of the active worksheet; if the cell contains a non-ASCII or a newline character, it changes the fill color of the cell. In the end a message mentions the total number of the highlighted cells.
    Try
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-25-2016
    Location
    Norway
    MS-Off Ver
    2013
    Posts
    8

    Re: VBA macro to count & highlight cells with non-ASCII characters

    thank you all for the help, jidon's macro looked to match my needs in a snap

    @jindon: May i ask how i replace newline character with a space (or delete it) ? Otherwise i can do it manually with ctrl + H, no need to put more time into this!

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: VBA macro to count & highlight cells with non-ASCII characters

    You are welcome and thanks for the rep.

    Just one line after the loop?
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-25-2016
    Location
    Norway
    MS-Off Ver
    2013
    Posts
    8

    Re: VBA macro to count & highlight cells with non-ASCII characters

    my mistake - i'd like the newline replaced by space, but then there is no need to highlight these cells

    i tried to remove \n from the Pattern... didn't turn out well :D (all cells turned red)

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: VBA macro to count & highlight cells with non-ASCII characters

    Have you tried to put that line before the loop?

    If so, can you upload a small sample workbook showing before/after that you want?

  8. #8
    Registered User
    Join Date
    04-25-2016
    Location
    Norway
    MS-Off Ver
    2013
    Posts
    8

    Re: VBA macro to count & highlight cells with non-ASCII characters

    SOLVED - i used another macro before yours, found here:

    edit:

    yep, putting the line before the loop also does the trick
    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    Thank you again for the help
    Last edited by Dimitris254; 09-20-2016 at 09:02 AM.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: VBA macro to count & highlight cells with non-ASCII characters

    OK.
    If you don't Carriage return to match then pattern should look like
    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] Convert ASCII characters to Hex values
    By Scott Rebman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-10-2014, 04:05 AM
  2. EXCEL 2010 create a macro- highlight any cells with more than 30 characters in a column
    By cluelessexcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2013, 11:00 AM
  3. [SOLVED] Highlight cells that are over 100 characters
    By jsz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-06-2013, 10:51 AM
  4. highlight cells that contain the same first 5 characters
    By goldstar1 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-17-2013, 02:24 PM
  5. ASCII or ANSI characters
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2007, 09:18 PM
  6. Identify certain ASCII characters in a text.
    By titushanke in forum Excel General
    Replies: 6
    Last Post: 02-06-2007, 08:30 AM
  7. [SOLVED] Replacing ascii characters
    By HappyCamper in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-08-2006, 03:10 PM

Tags for this Thread

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