How To Apply A Button To Clear Specific Cells In Excel
HTML-код
- Опубликовано: 7 ноя 2017
- How To Apply A Button To Clear Specific Cells In Excel?
Need to work on the same workbook again and again? If so, you must be deleting specific cells to update your data. Then why don’t you create a button to clear specific cells in a workbook? This is what I am going to do today. A simple “Clear Cell” button for you.
This one is advanced excel tutorial and we are going to use Excel VBA for this project. Again, I am using Microsoft excel 2010 for the demonstration. Let’s get started.
Step 1: Create A button by Clicking “Insert” and Then “Shapes”.
Step 2: Add text on the button as button name. You can write “Clear” As button name
Step 3: Add Excel VBA by clicking “Developer” and “Visual Basic”.
Step 4: Now from the new window. Click “insert” and then click “Module”
Step 5: Write the code here (Please subscribe and here goes the code: / 34608230 ) and click “Save”.
Step 6: You need to assign Macro to the button you created. Right click on the button and click “Assign Macro”
Step 7: Click on “Clear” and click “Ok”. (You need to select the button name here. Mine is “clear” so I clicked on that)
Done
This is how you create button to clear specific cells in excel. Hope you like it.
#excel10tutorial #clearbutton
Thanks for watching.
-------------------------------------------------------------------------------------------------------------
Support Us: / excel10tutorial
-------------------------------------------------------------------------------------------------------------
If you like this tutorial, please subscribe:
goo.gl/uL8fqQ
Checkout our Advance Excel Tutorial Playlist:
goo.gl/ExYy7v
Checkout the Excel tutorial for dummies playlist: goo.gl/UDrDcA
Checkout the Excel Case playlist:
goo.gl/xiP3tv
If you need any specific tutorial you can request that here: goo.gl/jM78fA
Connect us on social media
Facebook: goo.gl/hdJ7rN
Google Plus: goo.gl/Yk4DQc
Your tutorials are so good! I had been struggling to follow other online tutorials but yours are so easy to follow. Thank you very much for doing what you are doing and sharing your knowledge.
Thanks for your appreciation. This is all i need to keep going. Please subscribe and share these resources with your friends. This will help me a lot. Thanks again for excellent comment.
Thank you - Great teacher!
Thanks a million for your appreciation.
Thank you ! Great tip, very useful !!!
You're welcome
Thanks a lot for this video!
You're welcome. Please subscribe and share with friends. And thanks for the comment.
Thank you so much for this.
Now my work has become more faster to clear the data.
Thanks a bunch
I'm glad you like it.
@@Excel10tutorial Thank you
You're welcome
Thank you. This is great!
Glad it was helpful!
Thank you for this great video. I've been struggling to figure out how to do the last part. The internet isn't helpful. 👍🏻
Glad it helped. Let me know if you have any question. And please subscribe and support the channel.
GREAT VIDEO THANKS
You are welcome. Thanks a lot for your comment. Please subscribe and share with your friends. 😍😍😍
thank you ❤️ ..have applied your code and it's working :)
Thanks for your comment. Please subscribe and be with us
Very helpful. Thanks
You're most welcome. Please subscribe and share
Thank you. You provide easy to understand instructions, and I appreciate it.
I've not asked anyone online yet, but I have a question- If possible, how does one create a button to copy a range of cells if certain conditions are met?
Example-
If d6 is empty, copy a11:a17
If d6 contains text, copy b11:17
If d4 contains text but d6 doesn't, copy a11:a17 and c11:c12
If d4 and d6 contains text, copy b11:b17 and c11:c12
Thanks for your comment. I'm gonna have to take a look at your problem and it will take time. I'm having issues with my PC so can't work right now. I will get a new pc soon and after that i'll try to solve your problem. Please subscribe and be in touch.
Thank you very much.
You are welcome!
This is perfect. I am trying to do EXACTLY this but in Google sheets.
Ok, so I went back and tried it again. I created a macro as you did and by trial and error, I figured out how to apply the macro to the button. I would have NEVER considered messing with macros to apply to a button if not for you. Of all the tutorials I have seen, no one have ever so much as used the word "macro". All credit goes to this video. Thank you so much for knowing how to impart your knowledge!
Good to know.
Thank you so much! I want to learn how to enable another cell in the same sheet or another sheet (in the same Workbook) appear the same data of the data that we enter in first cell. Thank you!
Is this you are looking for? ruclips.net/video/JMqHwMQF-SY/видео.html
Great... Thanks..!
You're most welcome.
Is there any way to get it to clear the cells without clearing formatting (ie. lines, shading, etc.)?
Change Clear to ClearContents in the vba
Hello, this is a very good tutorial and thank you. I have 1 question, say i have a range of excel i wanted to clear but certain cells have to skip, is there a formula that i can use rather than writing range one at a time? For instance Range(“C1”, “C7”).clear but i need to skip c8 and c9 then include C10-C12. Is it possible to do it in 1 range? Thank you
Why you need to do it in one range? use the code in this tutorial and you can clear multiple cells those are not adjacent.
Very good.
Many many thanks
Is there a chance that i could clear only the number of the cells without losing the commands like vlookup/ifblank etc and the cell color
Hi how you doing. Great video. I need a piece of VBA code that clear automatically a particular row when the date set in that row/column has pass
i'll look into it and let you know. subscribe to be in touch
Hi! Love this tutorial! It was very easy to follow but I have another issue. I have the cells I want to clear color coded. So when I click CLEAR button, it also delete the box color and border. How can I keep my color and border and also use the clear button function?? Thanks!!
Yes you can do that. If you need to keep the formatting you'll have to write ClearContents in the code replacing Clear.
Excellent
Thank you so much 😀
thank you
You're welcome. Please subscribe and be with us.
thanx alot , it seems the clear code erases the cell lines aswell is there a code that erase the content of the cell only?
Remove Clear to ClearContent in the code and that will keep the formatting and only delete the value. Hope you got your solution. Please subscribe and share this video with your friends.
Great tutorial! One question I have is if there is any way to delete an entire row based on your selection and keep the same formatting?
Try This: ruclips.net/video/Q0rlo-mzZu0/видео.html
Thanks you this is great but can I make it clear single and merged cells in the same range?
Use this for merged Cell: ruclips.net/video/Te5S5B0lZMk/видео.html
And use you can add both in a single button
Great video. Is there codes possible to copy the whole sheet to another tab with the current date as the name for the copied tab and clear contents from the present sheet from the particular cells
Check this tutorial: ruclips.net/video/GAgoy_Of8nc/видео.html
Thanks, for the timesaving tip! How do I blank out the rows under 'Total' and 'Avg' columns after I have pressed the Clear button?
Add that data range inside the code also.
@@Excel10tutorial Thank you, that worked! Do you know how I can further modify this formula to alphabetize and omit duplicate values?
@@chris-zu6sf That is a different topic. you can use sorting and also remove duplicate from the data tab.
Great video. What if you only want to clear the content of cells within the range without changing the format of the cells?
First subscribe to the channel and then Change Clear to ClearContents in the vba
Use ClearContents method. Example: Range("A1", "A10").ClearContents
Very helpful video. In my spreadsheet on some occasions I have ranges of cells, but also a single cell that I need to clear. How to I program the single cell? It is not a Range...
Also, Merged Cells. How do I clear a range that consists of merged cells?
@@miam8903 Just write the cell name
Thanks, I am from indonesiap
You're welcome. Please subscribe and share the video.
Please subscribe If you can please Support Us: www.patreon.com/excel10tutorial
Signup here to get the code: forms.gle/H27wWWztmm6ezQZv6
Excel 10 tutorial
Where is the Code?
Where's the code???
Nice demonstration. How do you reference from other sheet. example: Make clear button on sheet 1 for sheet 2.
Will let you know shortly. Please subscribe.
Button in sheet 1 and the content will delete on sheet 2. Here goes the tutorial: ruclips.net/video/djEddntao9Q/видео.html
Bro thanks for your video. Clearly understood. Do have the code for save files in excel format to pdf ?
That is possible.
Where u do find the codes for this bro
Contact me through email with details of what you need.
hello, is there anyway to: add a specific text to a cell when the button is clicked? I really have to do this in my project and I don't know anything of vba , thanks a lot great vid
Yes that can be done.
Create a command button and write this code
Private Sub CommandButton1_Click()
Range("A1").Value = "the value"
End Sub
A1 is the cell where you need the value
great
Thanks
Thank you for the video, However, i want to clear value of cell D6 only which is a merged cell of D and E column. I am getting error "We can't do that on merged cell". Any suggestion to fix that?
Yes, correct. For merged cell check this tutorial: ruclips.net/video/Te5S5B0lZMk/видео.html
@@Excel10tutorial Thanks! Is it possible to clear both together with one button? I am trying to clear the following:
D6
H6
H7
H8
D8 (Merged with column E & F)
B13
C18
C19
C20
C21
C22
C23
C24
C25
C26
Great video. How do you delete cells on a different tab /sheet?
Here is how: ruclips.net/video/djEddntao9Q/видео.html
Great video,how we can email the worksheet we working from assigned button in same sheet
How do you usually sent?
You did not need to name all those ranges. You could simply write Range("b2:f7"). Clear that is all
Awesome tricks you just shared with the @excel10tutorial community. Thanks for that. It will really save time. Thanks a lot. Yet the way i demonstrated here will be helpful if we need to delete ranges that are separate from one another. Well at least that what i thought. Really appreciate your support. Thanks
It’s been very good. But I have a doubt is there a way by which you can hide the formlua that is seen even when you clear screen.
Please explain your requirement. If you need to hide formula you can check this tutorial: ruclips.net/video/pN9yA6gvWzw/видео.html
Thank You So Much! How do we clear specific cells (not range)? What is the code for it?
Just write the cell number in the code.
@@Excel10tutorial how?? Example
Thank you sir. It is easy to follow and I done it. but when I try to open again the file Clear Button doesn't work and have an error (Cannot run the macro. "filename'!Clearcells'. The macro may not be available in this workbook or all macros may be disable) then I check the Modules that I created but it is gone even I save it before closing the file.
Save the file in .xlsm format
@@Excel10tutorial thanks a lot sir.
Thanks a lot. But can it is excel 2007 also? Plz reply
You have to try it. Can't confirm if it will work on 2007. Make sure to tell us if it works on 2007
The title said "...to clear specific cells..." you did a range of cells. I was thinking more along the lines of deleting something like B3 and D8, specifically.
That is possible. You'll have to write that cell reference.
Hi, how do you save the coding, every time I open the file the coding is missing?
Save the workbook in .XLSM format which is Excel Macro Enabled Workbook.
How to make a button to clear cell data in another file or multiple file not the same file ( Working on Excel or GooGle Sheet ) ?
Try this: ruclips.net/video/jsi4Ip_vHnI/видео.html but it won't work on google sheet
What happens when we use the CLEAR ALL option. How to reverse this operation after using Clear All. can it be undone?
No it can't be undone. When you use vba there is no ctrl + z
Sir, how to clear the contents in the merged cells?
Here goes your solution: ruclips.net/video/Te5S5B0lZMk/видео.html
How can I delete specific cells in all sheets once a time using a button in one sheet
Add those cell references in the code.
Nice trick but i faced one issue..once sheet is closed then macro will be disabled. How to fix it..plz reply fast..
save the file in ,xlsm format and subscribe the channel 😜
I made the clear button but h
I am the only one who can perform the clear button. Can i assign other users to perform the clear function? Thank you
It will work on that workbook only.
hello sir, .clear vb is remove all my data validation but i don't want that. i only want to remove only text so is there other code can do it?
Check this one: ruclips.net/video/Q0rlo-mzZu0/видео.html
if I want to do this to multiplie sheets?
You can do that. Change the macro name for each sheet.
What if I want to turn back the cell to a previous value instead of clearing it?
If that value is fixed you can do that. if you just need to use undo then use CTRL+ Z instead
How to delete specific cell (randomly) in column?pls describe
Change This line
Range("", "").Clear
to
Range("B5").Clear
to only clear cell B5
What if the cell is in another sheet and the button is in another sheet? Thanks. Your video was very helpful!
Here you go. this code will clear cell c1 to c5 of sheet 2 and the button will be in sheet 1. Subscribe and enjoy
Sub Clearcells()
Worksheets("Sheet2").Activate
Range("C1","C5" ).Clear
End Sub
@@Excel10tutorial Thank very much! I have experiment your code and it seems that if I had multiple sheet and they had the same content in cells, all contents in the sheets will also be cleared. But I would try this code of yours now! Thanks.
@@jsmasaya9275 Try it and let me know if it works. Thanks for being with us
Here goes the video tutorial: ruclips.net/video/djEddntao9Q/видео.html
Help me button clear checkbox to uncheck box
How can i clear only the content of a cell and not everything (like formulas etc)?
Try this: ruclips.net/video/jsi4Ip_vHnI/видео.html
Hi, great video. How can i put a message "are you sure? yes or no" ?
Function ValidEntry_d() As Boolean
End Function
Sub Clearcells()
Dim d As VbMsgBoxResult
d = MsgBox("Are you sure?", vbYesNo + vbQuestion, "Clear cells")
If d = vbNo Then Exit Sub
If ValidEntry_d = True Then
End If
Range("A5", "A10000").ClearContents
Range("B5", "B10000").ClearContents
End Sub
HI..I WANT TO INSERT VAULE 0 AFTER CLEARING THE CELLS.CAN U PLZ SHARE THE CODE
Change Range("", "").Clear to Range("", "").Value = 0 in the code.
How can I make it so that it gets automatically clear at midnight for fresh data to be inputted next day?
I want to clear not only continuous cells also different cells like B12 or D6 or g11 etc. How is possible? Plz help
Change This line
Range("", "").Clear
to
Range("A1", "A5").Clear
to clear from cell a1 to a5. and change it to
Range("B5").Clear
to only clear cell B5
it cant delete merge cell. please let me know how thank you
Check these: ruclips.net/video/Te5S5B0lZMk/видео.html
One problem occurs during used this steps, but i want to keep formula so how can i solve problems?
Try this: ruclips.net/video/jsi4Ip_vHnI/видео.html
Yikes. This worked. I created a button to clear the cells and it worked great. The only problem is that the .clear function also wiped out all my formatting, color fills, data validation and conditional formatting. :(
If you need to keep the formatting you'll have to write ClearContents in the code replacing Clear.
Hi how come my macro doesn't stay saved? it works but then when I close out of excel and reopen it it says it doesn't find the macro
Save the file with .xlsm format. It will solve your problem.
Thank you for this wonderful session. But there is a problem, it clears all the things like data, formatting: border, colour etc.. I want to clear only data, not formatting like border, colour......Kindly do reply.
Change Clear to ClearContents in the vba
@@Excel10tutorial Thank you so much for your reply.
You're welcome.
On Request:
How To Apply A Button To Clear Specific Cells On Another Worksheet In Excel: ruclips.net/video/djEddntao9Q/видео.html
How to create a button to clear multiple merged cells in excel: ruclips.net/video/Te5S5B0lZMk/видео.html
How to retrieve the same using macro
Once you delete something using a macro, there is no turning back.
Nice tutorial. But it's a bad function for my use. Cannot be undone with "ctrl+z", and it also deletes borders.
Guess you'll need to use something else. Sorry if it didn't serve your purpose. But please subscribe and share with your friends. It might help them.
Yeah.. Same happened in my case. It cleared even Filled color, font size everything ll come to default
@@lokihere8542 change clear to "ClearContents" in the macro
@@atsbfatal9607 THANK YOU
What is the command to delete cell contents without deleting formatting?
Here is it, This will clear cell but keep he cell formatting: ruclips.net/video/Q0rlo-mzZu0/видео.html
How to create undo / redo button in excel by using macro
Can't undo if you use a vba.
But when sheet is locked and when we use this button.. the unlocked cells are being locked automatically..could you please suggest what could be done ??
Send me your file and explain your problem in detail over email. I'll see what i can do.
@@Excel10tutorial please provide your email ID
@@hyderabbas3794 You'll find it in the channel about section
how can we do this in mobile app
This only works on desktop. sorry.
It deleted the cell background colour and border, How could I do it so it would only delete the text?
Here is it: ruclips.net/video/Q0rlo-mzZu0/видео.html
Thanks, How could I save the module? every time I open the excel file the module isn't there.
Save the file in .xlsm format
Good Tutorial, however you might want to add the fact that you will want to save this as an .XLSM File and not an XLSX. failure to do so will clear out all your macros that you've worked so hard on.
Yep. You are absolutely Right.
Where is the formula to copy/paste? I can’t find it.
@awesome12699
Sub Clearcells()
'Excel 10 Tutorial
Range("", "").Clear
Range("", "").Clear
Range("", "").Clear
Range("", "").Clear
End Sub
@@Excel10tutorial send it
@@naveenkumarbp268
Sub Clearcells()
'Excel 10 Tutorial
Range("", "").Clear
Range("", "").Clear
Range("", "").Clear
Range("", "").Clear
End Sub
Nice... and I bet you are one of the few who thinks Chinese is an easy language to learn in school since you spelt it with an ease. :D
Ha ha ha😃😄😂
How can we clear only values not formula
Change Range("", "").Clear
to
Range("","" ).SpecialCells(xlCellTypeConstants).ClearContents
I want get to get code
Check the link in the video description.
Can't find the code here
link in the description.
It's not working for me. It says I have a syntax error
Check the code again. Make sure nothing is missing.
'
I have protected my sheet but have the cells I have unlocked become locked when i clear the contents ready to use again. I don't want to keep going through my sheet to unlock all the cells again. The sheet needs to be locked to stop accidental alterations
Check this: How To Protect All But Certain Cells In Excel Worksheet: ruclips.net/video/7_3WUZNFtxc/видео.html
@@Excel10tutorial I can do that but that is not my question. I have done that but when I add a button to clear contents it clears the unlocked cell and it becomes locked again. I want to stop this from happening
Can you send me a copy? i'll take a look.
@@Excel10tutorial yes but how
salman.y2k7@gmail.com
i cannot find the code
In the video description.
Can you delete a specific button using clear button?
Help
what type of button do you have?
@@Excel10tutorial Shape
@@Excel10tutorial Shape sir
I couldnt find the code
Link in the video description.
How to clear particular any cell without cell format change?
here is how to clear cell keeping the format: ruclips.net/video/Q0rlo-mzZu0/видео.html
@@Excel10tutorial if i not range want.. only cell.. then what is the coding
Change Range("B9:F20").ClearContents to Range("B9").ClearContents
Now it will only clear contents from cell B9
Please give me clearly cod
Code link is in the video description
Copy code?
Link in the description
instead write 4 lines of codes ...........write in 1 line ie b2:e7
Great
Thanks for the videos
I would like to set an expiry date for my excel work book and then an activator incase some one buys it at the end of the expiry. How can I do this using excel?
Or is there a way of putting something online to allow me activate the work book remotely without sharing another file with someone? I'll be glad to hear from you
Thank you
mwebazenicholas6@gmail.com is my email just incase it's needed
I've never done something like that. if i find anything like it i'll definitely create a video on it.
This clears all formatting. Thumbs Down :(
If you need to keep the formating you'll have to write ClearContents in the code replacing Clear. Thumbs up buddy. Thanks for the comment.
Actually my bad you don't even need that code you can just record the macro. Way easier. It writes the code for you.
Yes. You can do that with macro recorder. I've covered that topics on my excel vba programming course. You could follow the course.
If i want to redo this action or by mistake clik it then?
You can't. Because Its created with VBA
Hi,
Kindly share code
Check in the description