Assign name to use in macro

[valerie1]

Hello,

I recorded a macro to select a range of cells and then clear their contents. When I ran the macro, I got the error message "Compile error: Argument not optional"--I believe the string specifying the cells may have been too long. Following a suggestion on this board (#4 on this page), I tried to select the range of cells and assign a name to it in the name field to the left of the formula field (with the intention of using the name in my macro instead of recording it). With the range selected, I entered the name "BookQuantities." The name was assigned, but the full range of cells was not included. The cells I want to select are all in columns D-F and rows 39-701 (but not all those cells--different ones in each row), but when I selected "BookQuantities" from the name field, it only selected the cells through row 223. I tried two more times with the same result, then I went to Insert > Name > Define and selected BookQuantities, and then used "Refers to" to select the correct range of cells again. Now when I select "BookQuantities" from the name field, it only selects the cells through row 150. Do you have any idea what I'm doing wrong? Or do you know a better way to select those cells and clear their contents?

I was able to assign a different name to a much smaller range of cells without any problems.

I'm using Excel 2004 for Mac version 11.6.6. Please let me know if there's any other information that would help with this issue.

Thank you so much!!

Valerie

 

[hippiehacker]

can you use

Sub valerie()
Range("D39:F701").ClearContents
End Sub

to clear contents

 

[valerie1]

Thanks for your reply! No, I don't think that will work. I don't want to clear all the contents from D39:F701--within that range, there are some cells I want to clear, and some I want to leave alone. (Let me know if you need additional information).

Thanks again,
Valerie

 

[hippiehacker]

which contents should be deleted and which should stay in the cells?

 

[valerie1]

It varies by row. In some rows, I want to clear all three columns; in others, just the D or E column.

The cells that should not be deleted have an m-dash in the cell, and the cells that should be deleted are either blank or have a number in them.

Thank you!
Valerie

 

[hippiehacker]

to clear the contents of numeric cells try

Sub valerie()
For Each cell In Range("D39:F701")
 If Application.IsNumber(cell.Value2) Then cell.ClearContents
Next cell
End Sub

 

[JackDanIce]

Hi Valerie, it may be much easier to provide help if you posted the code you recorded?

 

[JackDanIce]

Based on what you've said so far, here's a guess at what you're after:

Sub Valerie1()
Dim cell As Range, clear_cell As Range, rng As Range
Application.ScreenUpdating = False
Set rng = Range("D39:F701")
    
For Each cell In rng
    If InStr(cell.Value, "m-") = 0 Then
        If clear_cell Is Nothing Then
            Set clear_cell = cell
        Else
            Set clear_cell = Union(cell, clear_cell)
        End If
    End If
Next cell
clear_cell.ClearContents
Application.ScreenUpdating = True
End Sub

Change part in red if the string value is something else

 

[valerie1]

hippiehacker and JackDanIce, thank you both so much for your help!! I tried this code and it worked like a charm:

Sub valerie()
For Each cell In Range("D39:F701")
If Application.IsNumber(cell.Value2) Then cell.ClearContents
Next cell
End Sub

Also, thanks for the tip about posting the code that didn't work--I'll definitely remember that in the future.

Thanks again!!

Valerie

 

[JackDanIce]

This code is actually faster:

Sub Valerie_2()
On error Resume Next
Range("D39:F701).SpecialCells.(xlNumbers).ClearContents
On Error Goto 0
End Sub