Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

how do I clear multiple cell content over multiple worksheets using a clear button?

0

I need to create a clear the week button. I have a daily work schedule with a work sheet for each day of the week. I want to clear the same number of cells in each of the workday sheets. I know how to create a button and clear cell content in one worksheet just not multiple.

Answer
Discuss

Answers

0

Syntax for clearing (deleting cell contents in) a range is straight forward.

Range("A2").ClearContents
or
Range("A2:D3").ClearContents

But if you want the range to be dynamic you need to know a little more about how Excel defines ranges. Simple: by their first and last cells. In the above example A2 is the first cell and D4 the last one. Everything in between is within the defined range.

So, the question comes up how to define a cell. Simple: by its coordinates.. But remember it's Row before Column - Row & Column - not Column and Row as in range names like "A2". So, in VBA syntax 

[A2] = Cells(2, "A")
actually, it should be
= Cells(2, 1)        ' because column A has the column number 1)

So, now you can understand the code below.

Private Sub WriteCode()

    Dim RangeToClear As Range

    Set RangeToClear = Range(Cells(2, "A"), Cells(3, "D"))
    ' or
    Set RangeToClear = Range(Cells(2, 1), Cells(3, 4))
    ' or
    Set RangeToClear = Range(Range("A2"), Range("D3"))
    RangeToClear.ClearContents

    ' or, without declaring the range:-
    Range(Cells(2, 1), Cells(3, 4)).ClearContents
    ' or
    Range("A2:D3").ClearContents
End Sub

To answer your question comprehensively, you can clear multiple cells by defining the coordinates of its first and last cells - fixed or dynamically - using these cells to define a range, and then clear the range.

And, just in case: if you are thinking of asking for help about that please first see how far you get with the above answer and then ask another question. Please don't pile more onto this thread.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login