1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel - Need simple macro

Discussion in 'Business Applications' started by alexfooty, Feb 3, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. alexfooty

    alexfooty Thread Starter

    Joined:
    Apr 23, 2007
    Messages:
    50
    I'm sure this is a simple task for you boffins out there.

    In Col A, I have a list consisting of a series of number ie
    24,56,23,98,34,77,01
    65,98,12,17,36,99,31
    etc
    Unfortunately, these lists of numbers are taken from several different sources and some contain spaces ie
    24,56,23,98,34, 77,01
    65,98, 12,17,36, 99,31
    etc
    I just need a macro, which I can then assign to a toolbar button, which eliminates these spaces.
    All the list is contained to Col A and the number of rows varies from, say, 18 rows up to possibly 25.000 rows.

    I'd greatly appreciate any help with this please.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,673
    First Name:
    Wayne
    you could just use trim - that removes spaces

    so in B1

    =trim(a1)
    then copy to all the cells
     
  3. alexfooty

    alexfooty Thread Starter

    Joined:
    Apr 23, 2007
    Messages:
    50
    Many thanks for your quick reply.
    I'm currently using TRIM but, I know it may sound lazy, having spent a great deal of time acculumating the list I would just like a toolbar button to click on.
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Select your range of cells and run the macro below. If you want the macro to automatically select the range we can modify the code to select all non-blank cells in Column A.

    Code:
    Sub RemoveSpaces()
    
    For Each vCell In Selection.Cells
    
    vCell.Value = Replace(vCell.Value, " ", "")
    
    Next vCell
    
    End Sub
    Regards,
    Rollin
     
  5. alexfooty

    alexfooty Thread Starter

    Joined:
    Apr 23, 2007
    Messages:
    50
    Brilliant - works perfectly.
    Many thanks for your help. Much appreciated
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/679130

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice