Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I merge two cells without deleting data from the cell? | Excel Discussion (Misc queries) | |||
Deleting blank cells in a column | Excel Discussion (Misc queries) | |||
deleting certain text from cells | Excel Worksheet Functions | |||
Deleting #N/A from cells... | Excel Discussion (Misc queries) | |||
delete values in several cells without deleting the formulas | Excel Discussion (Misc queries) |