VBA coding to clear contents from selected cells from selected worksheet

[Ankur Kumar]

Hi,


I want to clear from coloumn D6:D14 from sheet1 and B5:F5000 and H55000 from sheet2 in one click, i am attaching a sample workbook with it please help me out.





Thanks in advance

Ankur

 

[Chihiro]

Just use Macro Recorder.

Base syntax from recorder.

    Range("D6:D14").Select
    Selection.ClearContents
    Range("H5:P5000").Select
    Selection.ClearContents

Now, you want to make sure that it references specific sheet. So you'd qualify Range() with Sheets... And eliminate Select/Selection.

Ex:

Sub Demo()
Sheets("Sheet1").Range("D6:D14").ClearContents
Sheets("Sheet2").Range("H5:P5000").ClearContents
End Sub

 

[Ankur Kumar]

I want to add some text as when i hit on clear button it show a message before clear the content like "clear all contents from selected cells" and then "continue to clear" when i hit on ok then clear the contents.

 

[Belleke]

Like this?

 

[Ankur Kumar]

Yes , that's i need to do but i want to add this button in ribbon as when i click on excel option and customize ribbon and selecting macros from choose commands from then it is not showing there, please help

 

[Chihiro]

Really, you should layout your entire requirements from the get go.

See link for how to add custom tab tied to macro in your ribbon.
https://www.contextures.com/excelribbonaddcustomtab.html