Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    78

    Unanswered: Remove the minus sign

    I have a file that was exported to excel. I have about 20 columns, column A:J contains values

    Is there any way to remove all minus signs from these columns? I would like to do it with a Macro

    Thank you

    Joe

  2. #2
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Not too bad for this one.

    Code:
    Cells.Select
    
    Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False,SearchFormat:=False,ReplaceFormat:=False
    If you want to replace it with something else other than nothing, alter the Replacement="" to whatever you wish.

    HTH

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Quote Originally Posted by daveo61
    Not too bad for this one.

    Code:
    Cells.Select
    
    Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False,SearchFormat:=False,ReplaceFormat:=False
    If you want to replace it with something else other than nothing, alter the Replacement="" to whatever you wish.

    HTH
    you can even condense this to

    Code:
    Cells.Replace What:="-", Replacement:="", LookAt:=xlPart
    but now im just being picky

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Welcome back, Dave! And we like it when you are picky; it keeps us on our toes.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Quote Originally Posted by shades
    Welcome back, Dave! And we like it when you are picky; it keeps us on our toes.
    i just pop in now and again just now, back being a student for a year so i and being a bar man and having a 2 year old (today) to look after so i have virtually no time to comment on vba;

    anything i can do to help you guys will be great

    all the best

  6. #6
    Join Date
    Dec 2005
    Posts
    4
    Yeah. The condensed part is good but it all depends on you. You can use any of them.

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    a reason why i promote using the condensed code in this case is that a lot of the functionality here is already preset so hence not displaying them shortens the line and then improves readability, im also totally against selecting in vba when it is not required, this slows down code considerably and should be avoided at all costs.

    another good thing to do is to set screen updating off until the end of code,
    a small trick i occasionally use is to set up a dummy sheet which will let the users know whats happening, get the screen to update to display this screen and then switch it back off again.

    but then i did say i was being picky

Posting Permissions

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