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

    ClearSeeminglyEmpty Revisited (Excel XP-SP3)

    With help from this group I have a macro that clears the apostrophe or label hook out of cells in a worksheet. There is a range in each of 20 sheets (D4.O90) that should be either a number or a formula. Some users will use the space bar to remove a number and will leave the label hook thus causing other macro related problems.

    The original macro must have worked, but it's been a year and now when I go to use it, it keeps failing. The macro is as follows:

    Sub ClearSeeminglyEmpty()

    Dim oCell As Range
    Cells.Select
    For Each oCell In Selection.SpecialCells(xlCellTypeConstants) <font color=red>Note: this is the failure point</font color=red>
    If Trim(oCell) = "" Then
    oCell.Value = 0
    End If
    Next oCell
    Set oCell = Nothing
    End Sub

    Can you help me figure out why it is failing AND modify it so it runs for every sheet in the workbook except the ones named "instructions" and "upload".

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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ClearSeeminglyEmpty Revisited (Excel XP-SP3)

    First, a couple of questions:

    1- You have a note saying "Note: this is the failure point", but you don't say what the failure is. Are you getting some kind of error message.

    2- That statement will fail if there are no cells on the sheet that contain constants. Is that the case?

    3- The macro does not seem to be clearing the cells, it is setting them to zero. Is that what you want, or do you want them cleared?

    4- The macro works on all cells on at sheet, not the range D4:O90. Which do you want it to do?

    5- Could you upload a workbook that shows the failure and that can be used for testing?
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ClearSeeminglyEmpty Revisited (Excel XP-SP3)

    Is this what you are looking for?
    Steve

    <pre>Option Explicit
    Sub ClearSeeminglyEmpty()
    Dim wks As Worksheet
    Dim rCell As Range
    Dim rng As Range

    For Each wks In ActiveWorkbook.Worksheets
    If LCase(wks.Name) <> "instructions" And _
    LCase(wks.Name) <> "upload" Then
    Set rng = Nothing
    On Error Resume Next
    Set rng = wks.Range("D4:O90").SpecialCells(xlCellTypeConstan ts)
    On Error GoTo 0
    If Not rng Is Nothing Then
    For Each rCell In rng
    If Trim(rCell) = "" Then rCell.Clear
    Next
    End If
    End If
    Next
    Set rCell = Nothing
    Set rng = Nothing
    Set wks = Nothing
    End Sub</pre>


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

    Re: ClearSeeminglyEmpty Revisited (Excel XP-SP3)

    Legare, forgive me for being an idiot. This is crazy-busy season and I'm going a little <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    <hr>You have a note saying "Note: this is the failure point", but you don't say what the failure is. Are you getting some kind of error message.
    <hr>
    Yes, I was getting a Runtime error. But when I read the error message again (I was rushing and not paying attention) I realized it was because the sheet was protected. Duh!!! The macro actually works fine when the user pays attention! Geez!

    But I do have a question. Is there a way to get it run on all the spreadsheets in the workbook except for those I named before? Right now you have to select each sheet and run the macro. And this brings me to your question
    <hr>That statement will fail if there are no cells on the sheet that contain constants. Is that the case?<hr>
    Exactly what is considered a constant? Each of the 20 sheets is pre-populated with text cells and formulas but a user may or may not use all 20. So some sheets will only have the predefined headings, row names etc. Are those constant enough for the macro not to fail if we change the macro to run on all sheets? How would I tell it to run on all sheets except the 2 mentioned?
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ClearSeeminglyEmpty Revisited (Excel XP-SP3)

    Have you looked at Steve's macro? It works on all sheets except two named as you described. However, it also clears the cells, it does not set them to zero, and it works only on the range you specified in your original message. If that is not what you want, then it can be easily modified.
    Legare Coleman

Posting Permissions

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