+ Reply to Thread
Results 1 to 7 of 7

How to detect if any cell, in any tab, ends with a SPACE?

  1. #1
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Arrow How to detect if any cell, in any tab, ends with a SPACE?

    Hi All,

    I've been trying to write an Excel VBA script that goes through each cell and each tab, and pops up a message for each cell that ends with a space:
    "Warning: TAB NAME -Sheet 1-, CELL -P5- ENDS WITH A SPACE" for each cell with a space.

    The If statement below doesn't work, but that may be because I found a VB approach online that doesn't work in VBA. Not sure anyone can fix the below attempt, or if I'm way off?

    Please Login or Register  to view this content.
    (ps: I wouldn't want to automatically delete the spaces, since I need to report the spaces back to the person who made the XLS file.)

    Thanks so much,
    JMC

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: How to detect if any cell, in any tab, ends with a SPACE?

    I think this will work:
    Please Login or Register  to view this content.
    Note: Added "Address" to boil out where the cell is, not the contents of the cell. Change if needed.

  3. #3
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: How to detect if any cell, in any tab, ends with a SPACE?

    No need to loop through all the cells on each sheet, just find the ones that end in a space.

    If you're reporting the cells back to the maker, msgbox for each found cell probably isn't the best/most efficent method, but it is what this code does per your request.

    Please Login or Register  to view this content.
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  4. #4
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Re: How to detect if any cell, in any tab, ends with a SPACE?

    Walruseggman: Actually I'm cool with any approach (spitting out the Tab names and the cell ID's at the very end would be better actually, but I thought it would be harder to do....) I'm open to that approach, if you know how it's possible?
    PS: Thanks so much for the help so far, but the code you've sent unfortunately isn't catching cells ending with a space. I put an else statement at the end (see below), but it comes back every time saying "NO SPACES FOUND", even if I have cell C5="testsp ".
    Please Login or Register  to view this content.
    Jomili: Thanks so far for your help too. In order to get it working a little bit further, I needed to change the for statement to:
    Please Login or Register  to view this content.
    Although the above code is successfully catching the cells ending with a space, is there a way for it to not check "every" unused cell on the sheet? (because it seems to freeze Excel past the unused rows)
    - Also is there a way to cycle through all of the tabs as well, pls?
    Last edited by jmccoughlin; 04-05-2016 at 01:26 PM.

  5. #5
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Re: How to detect if any cell, in any tab, ends with a SPACE?

    Figured it out! Both were extremely helpful solutions, thank you both!

    As for Walruseggman it did the job FAST! ...it turns out to make it work, I needed change the for statement to: "For Each ws In ActiveWorkbook.Sheets"

    Please Login or Register  to view this content.
    Thanks again!
    -jmc

  6. #6
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: How to detect if any cell, in any tab, ends with a SPACE?

    For Each ws In ActiveWorkbook.Sheets
    Fair enough, the difference in the two is where you have the code and which workbook you're expecting it to run on. Glad you got that working.

    spitting out the Tab names and the cell ID's at the very end would be better actually
    Easy enough. I gave you two options at the end. The first spits out one big msgbox with all the found cells, the other puts the results into a cell on a sheet. You can change that cell and sheet to whatever you want. There are other options, of course. Just mention your ideal solution.

    For now, to switch between the two, comment out the msgbox and uncomment the Range (remove the '), or vice versa.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Re: How to detect if any cell, in any tab, ends with a SPACE?

    PS: For anyone else who stumbles upon this thread, I happened to come across a small bug in the above code. The above code will find "ANY" space in a cell, however the below code will find a space only "at the end" of the cell's text in any tab.

    So a quick change:

    Change:
    Set c = .Find("* ")
    to:
    Set c = .Find("* ", LookAt:=xlWhole)

    Please Login or Register  to view this content.
    Thanks so much to Walruseggman and Jomili!

    -JMC
    Last edited by jmccoughlin; 04-06-2016 at 02:50 PM.

+ 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] I need a macro to copy down till adjacent column ends in a blank cell
    By Paul Hayward in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-09-2014, 02:27 PM
  2. [SOLVED] Setting a range that ends where data ends?
    By Mgassma in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2013, 09:46 AM
  3. Replies: 4
    Last Post: 01-22-2013, 04:39 PM
  4. [SOLVED] If Cell Starts with a Space, remove that space
    By Ocean Zhang in forum Excel General
    Replies: 2
    Last Post: 09-29-2012, 01:52 PM
  5. Replies: 0
    Last Post: 10-25-2011, 12:19 AM
  6. Delete whole if the cell ends with something
    By mthem2003 in forum Excel General
    Replies: 4
    Last Post: 08-18-2009, 09:16 PM
  7. Adding many cell values to the ends of each other
    By eshell189 in forum Excel General
    Replies: 3
    Last Post: 10-02-2008, 01:40 PM
  8. Auto Insert Blank Row For Each Cell That Ends in....
    By ExcelQuestion in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-11-2007, 01:23 AM

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