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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
Code:
Sub test()
With ActiveSheet.UsedRange
     On Error Resume Next
     .Resize(.Rows.Count - 1).Offset(1).SpecialCells(2).ClearContents
End With
End Sub
 

mattlong

New Member
Joined
Jul 31, 2007
Messages
6
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!
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Can you explain what you want to do clearly?
Do you have any particular column(s) or something?
 

dlmslm

Board Regular
Joined
Jun 22, 2007
Messages
59
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:
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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.
 

mattlong

New Member
Joined
Jul 31, 2007
Messages
6
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!!
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

mattlong

New Member
Joined
Jul 31, 2007
Messages
6
PERFECT Jindon! That worked a treat!!! Your a true legend!

Thanks again for all your help!!
 

mattlong

New Member
Joined
Jul 31, 2007
Messages
6
PERFECT Jindon! That worked a treat!!! Your a true legend!

Thanks again for all your help!!
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,786
Latest member
Deniel

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
Top