Deleting Cells, but not ones with formulars

[scriblesvurt]

Hello,

I have a large sheet that people enter data into each week. I want to be able to hightlight a large area of the sheet and delete the data in those selected cells, but not the formulars that are in some of the cells.

Anyone know how I can do this?

Any help apreciated!

S

[mangesh_yadav]

This worked for me:

Sub DeleteCells()

Set rng = Range("A1:B2")

For Each cl In rng
MsgBox cl.Formula
If cl.Formula = True Then
cl.ClearContents
End If
Next


End Sub


Put the code in a standard module and run.


Mangesh

[scriblesvurt]

Hi,

This works apart from It puts up an OK box that you have to click for each cell. This is a lot of clicking to clear 200+ cells. Any way to change it so that it just runs right through?

Thanks,

S

[scriblesvurt]

Got it,

I just removed

MsgBox cl.Formula

Thanks for this!

S

[mangesh_yadav]

Sorry, forgot to remove the Msgbox statement.

Mangesh

[Dave Peterson]

Another way.

Select your cells to clean up.
Edit|goto|Special
click Constants
click ok
hit the delete key on the keyboard.

Don't select any cells that have labels you want to keep
(headers/instructions/etc).

scriblesvurt wrote:
>
> Hello,
>
> I have a large sheet that people enter data into each week. I want to
> be able to hightlight a large area of the sheet and delete the data in
> those selected cells, but not the formulars that are in some of the
> cells.
>
> Anyone know how I can do this?
>
> Any help apreciated!
>
> S
>
> --
> scriblesvurt
> ------------------------------------------------------------------------
> scriblesvurt's Profile: http://www.excelforum.com/member.php...o&userid=24399
> View this thread: http://www.excelforum.com/showthread...hreadid=380010

--

Dave Peterson

[scriblesvurt]

Thats great. Thanks!!