Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace Label Hook (Excel 2002)

    I have a macro that is failing because the user hit the space bar to blank out cells, thus leaving a label hook in the cell. I tried search and replace but it doesn't find the label hook ('). This was done in a multitude of sheets so I need a quick way to find them and replace with zero. Of course I wouldn't want to replace valid label hooks but if there is a way to find them in a range......

    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

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

    Re: Replace Label Hook (Excel 2002)

    This macro will clear all seemingly empty cells in the selection, i.e. cells containing an apostrophe, one or more spaces or an apostrophe followed by one or more spaces. It does not affect cells containing a formula.

    Sub ClearSeeminglyEmpty()
    Dim oCell As Range
    For Each oCell In Selection.SpecialCells(xlCellTypeConstants)
    If Trim(oCell) = "" Then
    oCell.ClearContents
    End If
    Next oCell
    Set oCell = Nothing
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Label Hook (Excel 2002)

    Hans,
    That's great! And it does clear the cells but what if I wanted those cells to be zero?
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Label Hook (Excel 2002)

    And... what if you wanted to select a range in all sheets and replace the "'" with zero?
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

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

    Re: Replace Label Hook (Excel 2002)

    If you want to set the value to 0, replace <code>oCell.ClearContents</code> by <code>oCell.Value = 0</code>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace Label Hook (Excel 2002)

    Well, what I did was Select All in each sheet (one at a time) and use your macro and then ran my original macro and it worked fine. Meaning that one of the things my macro does is check for empty cells and make them zero. So since your macro cleared the cells, my macro didn't fail and it put in the zeroes I needed.

    On the next workbook I will try to Select All after selecting all sheets and see if your macro will clear the label hooks.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  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 Label Hook (Excel 2002)

    Be careful with that, it will only work if the layout of those sheets is exactly identical (the ' are in the same cells). Otherwise, the macro might clear all cells on sheet 1 and since the sheets are grouped, will clear the SAME cells on the other sheets.
    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
  •