What is the correct code to delete only the values in a range?

[louvalterry]

I have a section of code I copied from another worksheet. In this one however all I want to delete is the data not the formating or formulas. The formulas are VLookup from another worksheet to cut down the data needing to be enterred. There seems to be a number of "Clearsomething" but not a ClearValue. How can I change the code below to only remove the data from the row that was copied and then inserted?

    myNum = Application.InputBox("Enter number of lines ", "How many Lines ?", 1, , , , , 1)
    Do While myNum > 0
    With ActiveCell
        .EntireRow.Copy
        .Offset(1, 0).EntireRow.insert Shift:=xlDown
         Range(.Offset(1, 1 - .Column), .Offset(1, 76 - .Column)).ClearContents
        .Offset(1, 0).Select
    End With
        myNum = myNum - 1
        Loop

[GarysStudent]

Replace you ClearContents line with this set of lines:

Dim r As Range
For Each r In Range(.Offset(1, 1 - .Column), .Offset(1, 76 - .Column))
    If r.HasFormula Then
    Else
        r.ClearContents
    End If
Next r

Its a matter of going cell-by-cell, leaving the formulas and clearing the constants

[louvalterry]

GaryStudent,

Thanks a lot this but to bed the last of my headaches. I have inserted the new code and it works like a charm. I hope my user does not have a problem with the couple of seconds it takes to step through all 76 cells.

[Paul_Hossler]

something like this would avoid looping

    On Error Resume Next
    Range(.Offset(1, 1 - .Column), .Offset(1, 76 - .Column)).SpecialCells(xlCellTypeFormulas).ClearContents
    On Error GoTo 0

Paul