PDA

View Full Version : What is the correct code to delete only the values in a range?



louvalterry
10-30-2013, 03:16 PM
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
10-30-2013, 03:45 PM
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
10-31-2013, 06:07 AM
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
10-31-2013, 06:29 AM
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