ClearSeeminglyEmpty Revisited (Excel XP-SP3)

Topic

New Reply

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) Note: this is the failure point
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

Reply | Quote

Replies

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?

Reply | Quote

Legare, forgive me for being an idiot. This is crazy-busy season and I’m going a little
[indent]

---

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.

---

[/indent] 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 [indent]

---

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

---

[/indent] 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?

Reply | Quote

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.

Reply | Quote

Is this what you are looking for?
Steve

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(xlCellTypeConstants)
            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

Reply | Quote