Deleting Cells, but not ones with formulars
 

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

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=380010

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
------------------------------------------------------------------------
scriblesvurt's Profile: http://www.excelforum.com/member.php...o&userid=24399
View this thread: http://www.excelforum.com/showthread...hreadid=380010

Got it,

I just removed

MsgBox cl.Formula

Thanks for this!

S


--
scriblesvurt
------------------------------------------------------------------------
scriblesvurt's Profile: http://www.excelforum.com/member.php...o&userid=24399
View this thread: http://www.excelforum.com/showthread...hreadid=380010

Sorry, forgot to remove the Msgbox statement.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=380010

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

Thats great. Thanks!!


--
scriblesvurt
------------------------------------------------------------------------
scriblesvurt's Profile: http://www.excelforum.com/member.php...o&userid=24399
View this thread: http://www.excelforum.com/showthread...hreadid=380010