Automatically Clear Contents of Worksheet without Clearing Formulas

How to automatically clear contents of a worksheet without deleting the formulas using VBA. Below is the complete VBA code:
Option Explicit
Sub removeMyConstants()
Dim myConstants As Range
Dim cel As Range
Set myConstants = Sheet1.Range("A1:F4").SpecialCells(xlCellTypeConstants)
On Error Resume Next
myConstants.ClearContents
For Each cel In Range("A1:F4")
If cel.Value = 0 Then
cel.NumberFormat = "#;#;"
End If
Next
End Sub

[@ashrafabdallaattyaelfeky2639]

Sir
Thank you for all the wonderful videos and your smooth explanation. We wish you more success and progress

[@Exceltrainingvideos]

Appreciate your motivational feedback.

[@lekkiezdasd]

hi sir, your video is very helpful, I have a question though, how I remove the contents or the formulas if not 0 the #;#; only works on 0. thank you

[@AbdullaKhizamworld]

Sir
I need to do the raw data sheet from Receipt. to receipt their is two form. one is directly paying cash at the time buying it and another is different format form which is credit settlement receipt.
so when ever any kind of payment received we get automatic rawdata with sequence order of all the details in the form.
thanks and hope I will get some kind idea or how it is used.
thanks 😊

[@Exceltrainingvideos]

Query not clear.

[@AbdullaKhizamworld]

@@Exceltrainingvideos Let's say I have a invoice.
when ever I create new invoice all the detail like product sold and qty and amount is saved on same workbook but different sheet plus invoice need to be saves as word in folder and also I can send those invoices via mail as PDF to the relevant client.

[@AbdullaKhizamworld]

@@Exceltrainingvideos Sir could you please help me on this? I have give explain below.

[@Whatisnottaken]

Thank you very much for the tuition. Everything worked fine. The formulas are still there except for the accounting format. Your help is appreciated .

[@latoyamartin3698]

Thank you sir. Is there a way to clear the value of the cells and keep the formulas with a dollar sign and dash?

[@ashwinisg7012]

Sir, How to clear the data once the macro is completed it's activity?

[@sayedyaqub2326]

How to Clear contains only without clearing format?

[@Exceltrainingvideos]

Let's say we have formatted text in Range A1:
Range("A1").ClearContents
The above code will clear the contents without clearing the formats. We can now enter data into range A1. It will have the earlier formatting.

[@nvcken]

that cool, how can work without clearing Data validation (dropdown) as well?

[@Exceltrainingvideos]

Good question!
Range("A1:B3").ClearContents
Range A1 has data validation and the other cells have some data.

[@danemcleod7667]

hi how do I clear a data base of events list every year between April 28 and April 30th it should prompt the user if they want to clear data to start the new year if the user answer is "Yes" then vba clear data base and set the prompt for the next up coming year, if "No" then when the sheet activate it should continue prompt to clear data until user clear that by April 30th

[@Exceltrainingvideos]

These two links will guide: www.exceltrainingvideos.com/quick-and-easy-inventory/
www.exceltrainingvideos.com/generate-report-based-on-dates/
Or search for more VBA tutorials on the topic: www.exceltrainingvideos.com

[@dijanaduran]

Hello, I have one question. How can I clear contents without deleting also format of every cell? Now I can't delete formulas but shape of cell is also lost when i press button Clean with your code. Thank you for your help!

[@Exceltrainingvideos]

Make a copy of your file and try, for example:
Range("A1").ClearContents

[@ganeshbatcha577]

Sir in mobile app wt can we do

[@viribusgym2216]

thank you for this video.
if there is no problem i have one more question.
my searchable drop down list don't work every time, it work in specific cells and other cells doesn't work, Same Data Validation work in a sheet but if i used in other sheet doesn't work.
There is any solution to make a searchable and clickable drop down list using vba or other methods ?
Thank you

[@Exceltrainingvideos]

Can you share your code?

[@suchitagaikwad755]

Hello sir ,
I have written same code but it not clearing cell data and while running the code it does not show any error. Please answer

[@Exceltrainingvideos]

The VBA code is shared in the description. Please check again.

[@ohitaodu-thomas8521]

Thank you so much ! This formula just saved me a whole lot of work. I previously was using codes that took away the formulas but this just did the trick!

[@Exceltrainingvideos]

Fantastic! Please share with your friends too.

[@alexandergonzalojimenezcas3984]

You're my hero Dinish greetings from Peru, excelent

[@Exceltrainingvideos]

Thank you!

[@sandeepkumar-cx4or]

Wondorfull sir ji your are great i am big fan of you

[@Exceltrainingvideos]

Thank you for the feedback - great motivation.

[@anshulgupta3735]

Hello Sir, Can I use name of the named range instead of cell range in the range arugment?

[@Exceltrainingvideos]

Yes.

[@anshulgupta3735]

@@Exceltrainingvideos Thank you sir. I tried and it worked. :)

[@leon436100]

You are amazing human being. The code is so concise!

[@Exceltrainingvideos]

Glad the VBA code helped. Please share with your buddies.

[@leon436100]

Dinesh Kumar Takyar sure!

[@arvindgupta5457]

Grt dinesh ji

[@Exceltrainingvideos]

Thanks.