Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace with nothing (98)

    Hi All,

    How do I replace all fields in a range with a text value of ie. # to 'NOTHING'. When I use the code below it replaces the value with ' (which means a text field) and then I cannot count the actual values I need to count. Basically the cells should be numeric format after the replace.

    Selection.CurrentRegion.Select
    Cells.Replace What:="#", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False

    Thanks a lot
    Rene

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace with nothing (98)

    I have several remarks:
    <UL><LI>Your code can be reduced to

    Selection.CurrentRegion.Replace What:="#", Replacement:="", _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

    This avoids selecting a range, which usually is not necessary for the correct functioning of a macro, amd slows down execution.
    <LI>Since you have LookAt:=xlPart, this will replace # as part of a cell value, so Issue#5 will become Issue5. I don't know if this is what you intend.
    <LI>If you have cells that contained '# to begin with, the replace will leave the apostroph, so you end up with '. If this is the case, you can get rid of apostrophs at the beginning of a cell by the following trick (from Andrew Cronnolly, see <post#=142352>post 142352</post#>):
    <UL><LI>Select a blank cell.
    <LI>Copy it to the clipboard
    <LI>Select the range that contains cells starting with an apostroph.
    <LI>Select Edit/Paste Special...
    <LI>Click the Add option, then click OK.[/list][/list]HTH

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace with nothing (98)

    Thank you Hans,
    That part now works 100%, but I need to put an "IF" statement in my macro to replace only the blank cells in my range and not to overwright the cells with actual values in them. Can you please help with the code?
    Thanks once again
    Rene

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace with nothing (98)

    I'm sorry, I don't understand how this relates to your previous question - there, you were trying to replace # with (nothing). What do you mean by "replace only blank cells in my range"? Do you want to fill the empty cells in a range with something? If so, with what? A constant value, or a formula?

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace with nothing (98)

    OK, Let me try to explain.
    1. I replaced all values # with "" in a column, but not all the cells contain #, some cells contain valid values.
    2. After the replacement the empty cells contain ' apostrophe.
    3. I followed your advise on how to replace the apostrophe but I need to programatically replace the apostrophe with blanks but not overwright the valid values with blanks.
    4. That is why I need the IF statement: If C1:C50 = apostrophe THEN copy/paste special as per your previous advise.
    I hope this makes more sense
    Thanks a lot
    Rene

  6. #6
    New Lounger
    Join Date
    Jun 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace with nothing (98)

    Post deleted by RENEVG

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace with nothing (98)

    Wouldn't this suffice:

    Selection.Replace What:="#", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False
    Selection.Replace What:="'", Replacement:="", LookAt:=xlWhole, SearchOrder _
    :=xlByRows, MatchCase:=False
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace with nothing (98)

    Umm, you seem to have posted two versions of this reply. Maybe you can delete the one I'm not replying to - <post#=227140>post 227140</post#>.

    The problem is that Find/Replace can't be used to find cells that start with an apostrophe.

    If your cells contain constant values, Paste Special a blank cell with the Add option won't modify their values (but it might change a date to a number) - the number 37 stays 37, the text Sample remains Sample, TRUE stays TRUE. So in most cases it does no harm to "add" a blank cell to constant values. It's different for formulas -they will be changed.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace with nothing (98)

    No it wouldn't <g>.

    This might do it:

    Dim rCell as Range
    For Each rCell in Selection
    if rCell.Value="#" or rCell.Value="'#" Then
    rCell.ClearContents
    End If
    Next
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    New Lounger
    Join Date
    Jun 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace with nothing (98)

    Hi Hans & Jan Karel,
    Thanks you both for helping me. At the end of the day I used Jan Karel suggestion for my problem but Hans' solution can be used in other areas.

    Thanks a lot
    Rene

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace with nothing (98)

    Note that my solution doesn't check whether a cell has a formula (resulting in the string looked for) or if it is just text in the cell!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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