clear/delete cells without formulas

mattlong

New Member
Joined
Jul 31, 2007
Messages
6
Hi,

Im trying to make life easier by setting up some sort of macro function that will clear the contents of all the cells except for the ones that have formulas in them.
I don't want to delete the 1st row as it contains the titles for each column.

Any ideas?

Many thanks!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi
Code:
Sub test()
With ActiveSheet.UsedRange
     On Error Resume Next
     .Resize(.Rows.Count - 1).Offset(1).SpecialCells(2).ClearContents
End With
End Sub
 
Upvote 0
Thanks Jindon!

Works great, however it has cleared more fields than I anticipated. Is there any way I can add key words to exclude from the deletion?

Thanks!
 
Upvote 0
Can you explain what you want to do clearly?
Do you have any particular column(s) or something?
 
Upvote 0
Hi jindon
I was reading your reply to matlong, and I need some help
I tried using your code and could not get it to work, i use Excel 2000, Windows XP(home). I opened the excel file, went to the sheet I wanted to use the code on, opened up view code, first drop down box i chose worksheet, 2nd box chose Activate, copied your code and pasted it into the box, closed out and went back to sheet, highlighted entire sheet, clicked delete, everything deleted including the formula i had. what did i do wrong in pasting the code, have had very little experince in using VBA.
you may have to take me step by step. :rolleyes:
Thanks for you help!

Dan :cool:
 
Upvote 0
Hi jindon
I was reading your reply to matlong, and I need some help
I tried using your code and could not get it to work, i use Excel 2000, Windows XP(home). I opened the excel file, went to the sheet I wanted to use the code on, opened up view code, first drop down box i chose worksheet, 2nd box chose Activate, copied your code and pasted it into the box, closed out and went back to sheet, highlighted entire sheet, clicked delete, everything deleted including the formula i had. what did i do wrong in pasting the code, have had very little experince in using VBA.
you may have to take me step by step. :rolleyes:
Thanks for you help!

Dan :cool:
What you are describing is setting up event code. It would probably be best if you need more explanation than this to create a new thread. In short what event code does is trigger every time that specific event occurs (in this case everytime the sheet is activated). You will not need to delete anything, the code will do it.
 
Upvote 0
Hi,

The cells I don't want to delete contain text like "subtitle", another couple are abbreviations like "MT" & "MB". These are the cells I want to keep populated.

I would like to customise the script to add or delete words at any time.

Unfortunately, the cells which contain these headings always change rows(having said that, the columns don't change, but those columns also contain formulas, which I don't want to delete).

Really what I'm asking for is a script that will avoid deleting the contents of the cells containing the keywords (ie "subtotal") from worksheet.

Thanks for your help!!
 
Upvote 0
Then you need to loop
Code:
Sub test()
Dim r As Range
With ActiveSheet.UsedRange
     On Error Resume Next
     With .Resize(.Rows.Count - 1).Offset(1).SpecialCells(2)
          For Each r In .Cells
               If Len(r.Value) = Len(Replace(Replace(Replace(r.Value, "subtotal",""),"MT", ""), "MB", "")) Then
                    r.ClearContents
               End If
          Next
     End With
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,218,576
Messages
6,143,315
Members
450,477
Latest member
teresab543

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top