Delete Contents of a Range 1

[vestax22]

Hi, I just want to know if its possible to select a range and delete iss contents if it does not contain a formula. In other words i want to delete the contents of the range but leave the formulas intact. Is this possible??


Thx in advance

 

[xlbo]

Range("Rangename&quot.specialcells(xlcelltypeconstants).clearcontents

Should do the trick

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[vestax22]

Thx a lot, works perfectly

 

[vestax22]

One thing though,

When there are no values inside the range it crashes. Usualy clearcontents will work even if there is nothing to clear inside the range. Why does it crash when there are no values to erase?


thx

 

[xlbo]

probably 'cos it's using the specialcells syntax - tho I can't be sure

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Loomah]

The error is created because there are no constant type cells to use the clear contents method on. If this is likely to be the case then error trapping will be necessary, eg

Sub a()
On Error GoTo mash
Cells.SpecialCells(xlCellTypeConstants).ClearContents
MsgBox "Continuing Code!!"
mash:
If Err.Number = 1004 And Err.Description = "No cells were found." Then
MsgBox "There are no constant cells to select"
Resume Next
End If
End Sub

Happy Friday!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244