Clear Content

[Smurfit-Stone]

Hello Board,

I need a macro that will clear the content of multiple range names. I do not want to delete the range names, just clear the content in them. I need to clear a spreadsht daily to enter new data, there are 13 range names, that need to be cleared. Is there a easy way to do this? Thanks in advance.

 

[Boller]

If you are talking about a VBA solution :-

Range(rangeName).ClearContents

 

[Smurfit-Stone]

Yes, I need a macro to clear the content of 13 range names on a daily basis.

 

[MrKowz]

What are the names of the ranges, and when do you want them cleared? When the workbook opens? When the workbook is closed? At a specific time?

 

[Smurfit-Stone]

I want to assign the macro to a button and let the user clear the content whenever their done with printing, emailing, etc. The range names are as follows.

Unit123
Unit122
Unit120
Unit104
Unit125
Unit128
Unit133
Unit135
Unit140
UnitSlitter
UnitWet
UnitDry
UnitPost

 

[MrKowz]

Assign the following macro to your desired button:

Public Sub ClearRanges()
Range("Unit123", "Unit122", "Unit120", "Unit104", "Unit125", _
      "Unit128", "Unit133", "Unit135", "Unit140", "UnitSlitter", _
      "UnitWet", "UnitDry", "UnitPost").ClearContents
End Sub

 

[Smurfit-Stone]

Thank You...it cleared the contents, but it gave me a error message that says "wrong number of arguments or invalid property assignments", what does this mean and what should I do? Thanks again!

 

[Smurfit-Stone]

I'm sorry, but it actually didn't clear the contents, I was wrong. I was looking at the wrong file.

 

[Norie]

Add a button to the worksheet, either a Forms button or an ActiveX button (from the Control Toolbox.

For the Forms button, right click and select Assign Macro... and then click New.

You should see something like this:

Option Explicit
 
Sub Button1_Click()
 
End Sub

Note, some versions of VBA will go straight to the Assign macro part as soon as you add the button.

For the ActiveX button, double click it.

You should now see something like this:

Option Explicit
 
Private Sub CommandButton1_Click()
 
End Sub

Now for whatever type of button you've added, add this code.

Dim arrRanges
    arrRanges = Array("Unit123", "Unit122", "Unit120", "Unit104", "Unit125", "Unit128", "Unit133", _
                            "Unit135", "Unit140", "UnitSlitter", "UnitWet", "UnitDry", "UnitPost")
    
    Range(Join(arrRanges, ",")).ClearContents

You can make changes to the array, eg add names, change names, as needed.

 

[Boller]

Assign the following macro to your desired button:

Public Sub ClearRanges()
Range("Unit123", "Unit122", "Unit120", "Unit104", "Unit125", _
      "Unit128", "Unit133", "Unit135", "Unit140", "UnitSlitter", _
      "UnitWet", "UnitDry", "UnitPost").ClearContents
End Sub

I think that should probably be :-

Range("Unit123, Unit122, Unit120, Unit104, Unit125, _
Unit128, Unit133,Unit135, Unit140, UnitSlitter, _
UnitWet, UnitDry, UnitPost").ClearContents