Free Microsoft Excel 2013 Quick Reference


Dear Experts

In sheets("Report-1"), I want to clear the contents from Range A6 to LastRow.

(content clear from all columns)

Please help


I am trying to clear the contents from multiple columns in a table that are not next to each other.

Right now I have:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there a way to combine this into one line?

I have the code as follows to clear a range of cells:

Range(Cells(r, c), Cells(65536, c)).ClearContents 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but I seem to be unable to have it clear just one sell, which has the name location, I have looked and tried a number of options, none of which work?

any ideas ?


I've used target before actually a much mor complex version with elseif and rtarget global references. I can't figure out why my else statement is returning this error. All i want is for the spaces that were filled in with data when a choice was made from the validation list to be erased if the taget cell is cleared. Here's the code

    Dim Add As String, Size As String, Sch As String 
     'On Error Resume Next
    With Range("c11:stop").Validation 
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
        xlBetween, Formula1:="=NameList" 
        .IgnoreBlank = True 
        .InCellDropdown = True 
        .InputTitle = "" 
        .ErrorTitle = "" 
        .InputMessage = "" 
        .ErrorMessage = "" 
        .ShowInput = True 
        .ShowError = False 
    End With 
    If Not Intersect(Range("c11:stop"), Target) Is Nothing Then 
        Target.Offset(, -1).FormulaR1C1 = "=VLOOKUP(RC[1]:R[50]C[1],Material!C[0]:C[3],2,FALSE)" 
        Target.Offset(, 18).FormulaR1C1 = "=VLOOKUP(RC[-18]:R[50]C[-18],Material!C[-19]:C[-16],3,FALSE)" 
        Target.Offset(, 15).FormulaR1C1 = "=RC[-14]+RC[-12]+RC[-10]+RC[-8]+RC[-6]+RC[-4]+RC[-2]" 
        Target.Offset(, 16).FormulaR1C1 = "=RC[-12]+RC[-10]+RC[-8]+RC[-6]+RC[-4]+RC[-2]" 
        Target.Offset(, 17).FormulaR1C1 = "=RC[-15]" 
         'Target.Offset(, -1).clearcontents
         'If i try to unblock the above I get an apllication or object error
         'I can't even select - columns to the target only positives
         'Target.Offset(, 19).FormulaR1C1 = "=RC[-1]*RC[-9]"
         'Target.Offset(, -1) = Target.Offset(-1, -1).Value + 1
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have a lookup working from a data validation list. When a user selects a new value, I want to clear a cell and a range that relate to the previous selection so are no longer valid.

I use a Worksheet_change evet to trigger the clean up:

    If Target.Address = "$C$3" Then 
        Application.EnableEvents = False 
    End If 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

     'MsgBox ("Clear!")
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This all works fine in Excel2000, but in Excel2002 the cell and the range don't clear. By un-commenting the MsgBox, I can show that clean_up is being called - so the event is firing as expected. And clean_up does work correctly when it is called from other places in the application.

Anyone any ideas?

Hi all,

I wrote a marco in Excel 2003 , but I find it can not run in Excel 97 .

My marco include functions of clearcontents and refresh pivot table. I can run it smoothly in Excel 2003, but run-time error / program failure occured when I use Excel 97 to run . The code is as follow . Could anyone please help me ? Thx a lot

Private Sub Snapshot_Button_Click()

Application.ScreenUpdating = False
' Get last row
Dim last_row As Integer
last_row = Get_Last_Row

' Clear last snapshot contents
' Worksheets(snapshot_sheet).Range("Snapshot_Range").ClearContents

' Copy RIC code
Worksheets(snapshot_sheet).Cells(1, 2) = Now
Worksheets(data_sheet).Range("A5:A" & last_row).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

' Copy Volume
Worksheets(data_sheet).Range("D5:D" & last_row).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

' Refresh pivot table
'Worksheets(snapshot_sheet).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'Application.ScreenUpdating = True

End Sub

I have written a Excel-VB based monte carlo simulation program. But Excel 2003 takes several seconds to clear the contents of worksheets on which I have (1) both written 10,000 rows by 50-100 columns of randomly-generated data, (2) sorted each column individually using Excel's "sort" worksheet function, and (3) repeated (1) and (2) multiple times.

I have done the following to speed up the macro:

    .Calculation = xlCalculationManual 
    .ScreenUpdating = False 
    .EnableEvents = False 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Of course, if I didn't at least do this much, it would take minutes to clear. But even with these commands, clearcontents is still very slow. Indeed, the more repetitions I do of steps 1 & 2 above before I execute the clearcontents command, the longer it takes Excel to clear the contents. To put it another way, the more I manipulate the data on a worksheet before executing a clearcontents command, the longer it takes Excel to clear the contents.

It also takes Excel equally long to delete the worksheet.

I wonder if Excel is storing the macro's changes to the worksheet in some undo buffer, so that the more Excel worksheet functions I do to the spreadsheet (from within the macro), the longer it takes to clear the contents of the spreadsheet. Now, I've read that Excel clears the undo buffer or stack every time a macro runs. But does Excel entirely disable the undo buffer/stack while the macro is running? If not, is it possible that Excel is filling up the undo buffer while the macro is running every time I perform an Excel "worksheetfunction" on that spreadsheet? If so, is there a way, within an Excel VB subroutine, to temporarily disable the undo buffer/stack? If not, what else can I do to speed up the clearcontents command?

To avoid this particular problem, I could perform the sorts on subroutine arrays instead of writing them to a spreadsheet and using Excel's "sort" worksheetfunction. But Excel's "sort" worksheetfunction is an extremely fast piece of code -- much faster, I think, than anything I can achieve using even a recursive VBA function. (And I haven't been able to get Excel's "sort" function to work with VB arrays).

Any insight would be greatly appreciated.

I'm unable to get ClearContents to work on a named range when that range consists of merged cells.

For example, if I have merged the range A1:B1 and have named it XYZ, the following VBA script (regardless of where I put it - eg in a module etc) gives the error "Cannot change part of a merged cell":


Yet if I use the following VBA script, it works perfectly:


I would like to be able to use the first version with the named range so that if I was to insert rows and columns (such that the range is no longer located at A1:B1) then I don't have to remember to update my VBA script with the new cell reference. Is this possible or is there a workaround? It appears to be the merged cells that are causing the problem, however, I need to have the cells merged due to other formatting/page layout issues.

Many thanks in advance.

I have a little problem...

When I clear the content when selecting "yes". the formula doesn't clear. Here is my worksheet VB codes:

    Application.EnableEvents = False 
    If Not Intersect(Target, Range("B5:B30, C5:C30, D5:D30")) Is Nothing Then 
    End If 
    Application.EnableEvents = True 
End Sub 
Sub test() 
    MsgBox Application.WorksheetFunction.Match(Range("A5:A1000"), Worksheets("Report").Range("A5:A1000")) 
End Sub 
Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim NewRwHt As Single 
    Dim cWdth As Single, MrgeWdth As Single 
    Dim c As Range, cc As Range 
    Dim ma As Range 
    Dim TCol As Long 
    Dim TRow As Long 
    Dim RptProjRowNum As Long 
    TCol = Target.Column 
    TRow = Target.Row 
    If TCol = 1 Then 
        Cells(Target.Row, "D").FormulaR1C1 = "=IF(RC3="""","""",RC3+90)" 
    End If 
    If TRow > 5 Or TRow < 20 Then 
        If Not Intersect(Target, Range("F:F")) Is Nothing Then 
            If Target.Cells.Count = 1 Then ' stops the code looping
                If LCase(Target.Value) = "yes" Then 
                    Range(Cells(Target.Row, 1), Cells(Target.Row, 1)).Copy 
                    Sheets("Evaluation").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues 
                     'Returns the row number of the same Project number on Reports Sheet.
                    RptProjRowNum = Application.WorksheetFunction.Match( _ 
                    ActiveSheet.Range("A" & TRow).Value, _ 
                    Worksheets("Report").Range("A5:A1000"), 0) + 4 
                     'Date Posted, Closing Date, Bill Validity
                    Worksheets("Report").Range("F" & RptProjRowNum & ":H" & RptProjRowNum).Value = _ 
                    Range("B" & TRow & ":" & "D" & TRow).Value 
                    Worksheets("Report").Range("R" & RptProjRowNum).Value = _ 
                    Worksheets("Report").Range("R" & RptProjRowNum).Value & ". " & _ 
                    Range("E" & TRow).Value 
                End If 
                Range(Cells(Target.Row, 1), Cells(Target.Row, 6)).ClearContents 
            End If 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have to manually select the cell and delete it... What am I doing wrong?


I hav a sheet with data in columns A to BA. If there is no data in column K i want to delete that row. I know the following macro works:

If Range ("K1") = "" Then Range ("A1:BA1").ClearContents

But I have 468 rows and dont fancy typing it out. Is there an easier way?

Thanks for any help.

Hi there,

I've been a long time reader, first time poster. I have a question I was hoping you guys could help me with - I have a (roughly ~1000 row) sheet (Transmission) that I'd like to search through and only keep rows where the value in column G is present in another list of values I've got (column C, in Template). Currently, I'm using this:

Set MySheet = Sheets("Transmission")
    LR = MySheet.Cells(Rows.Count, "G").End(xlUp).Row
    For i = LR To 2 Step -1
        With Sheets("Template")
            If WorksheetFunction.CountIf(.Range("C:C"), MySheet.Cells(i, "G")) = 0 Then
           End If
        End With
    Next i
And then I sort it to move the blank rows to the bottom. Now this works fine, but the problem is it's pretty slow (takes about 7 min, in total). Is there any obvious way I'd be able to speed it up, or will I just have to grin and bear it?


I have a function written that compares 3 cells. I would like to add into it that if both A & B are "" then have the function clear the contents of Cell C.

Function Balance(A As Range, B As Range, C As Range)
    If (Abs(A - B)

Long time visitor, first time poster! I hope someone can help with this as it's driving me slightly barmy.

I have a problem that when I run a macro from a created Excel Add-In I receive the message "Application-defined or object defined error" however I don't get this error when I run the code directly from VB.

I have narrowed the problem down to the following line Range("CLEARAREA").ClearContents which is obviously attempting to clear down the specified range.

Any ideas why this is happening? I am able to perform other actions to this same range such as clear validations etc.

I am trying, when a certain cell changes, to delete the contents of a number of other cells.

Because the particular cells will vary, I am referring to them indirectly - named cells contain the Excel references to both the "trigger" cell and the "to erase" cells.

Thus the start of my VB macro is:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim clrRef As String

clrRef = Range("TriggerCell").Cells.Text

If Not Intersect(Target, Range(clrRef)) Is Nothing Then

This works. If picks up the reference to the triggering cell as a text string in the cell named "TriggerCell" and then finds thta cell itself through Range(clrRef).

I use the same trick to figure out what cells need to be cleared

Dim clrCells As String
clrCells = Range("ClearArea").Cells.Text

For Each c In Range(clrCells)



End If

This also works, to pick up the proper range of cells whose refernce is recorded as a text string in the cell named "ClearArea". I've stepped through the debugger and seen that 'c' in the For loop properly takes each cell value in turn from the list provided in the "ClearArea"-named cell.

Here's the problem -- I CAN'T GET THE CELL VALUES TO CLEAR!

I've tried everything I can think of in place of ????????
c.Value = ""
c.Value2 = ""
c.Cells.Value = ""
c.Cells(1,1).Value = ""
Range(c.Address).Value = ""

I tried no for loop, and just did:

Nothing works. Every cell, each correctly found in turn by 'c', remains unaffected.

What should I be doing?!


Hoping someone can help me fill in the missing piece of code.

I have a listbox that is filled with the contents of a rage in a worksheet, (sheet 1)-A2:A30. Also, a commandbutton that would allow the user to delete the contents of the selected item in listbox along with the corresponding cell within the worksheet range. When I execute the commandbutton it always deletes the contents of cell A1........

Private Sub CommandButton3_Click()
For X = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(X) = True Then
End If
Next X
End Sub

Can someone point me in the right direction

Hello Members, I have the following code below that Vog II has given me.
It prompts a userform to show and clears the cell if a B upper or lower case is entered more than twice in a givin row. The code works for me.

Can it be altered to also clear the cell to the left of it when a B is entered. I played around with it but cant get it to work.


If Intersect(Target, Range("F7:T20")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If UCase(Target.Value) = "B" Then
    count = WorksheetFunction.CountIf(Range("F" & Target.Row & ":T" & Target.Row), "B")
    If count > 1 Then
        UserForm5.Show vbModeless
        'USERFORM WILL SAY "Only One ' BI ' is Allowed as Rounds are Played in a Horseshoe Game Session"
     End If
    End If
Regards, Tom

I want to write a macro that will clear the contents of my entire sheet if any number in cells A2-A35 is equal to zero. I also want this to repeat every time a zero is found in cell range A2-A35 and clearcontents of entire sheet. I have a web query that feeds machine data into the other cells on the sheet and in cells A2-A35 it is a countdown of the process that gets updated every minute once the process finishes and the counter in cloumn A reaches 0 I want to clear the entire sheet. The problem I have is the counter may reach 0 in cell A2 or A10 or A22 so anytime a zero is produced in range A2-A35 I want the entire sheet cleared.
Thanks, Cmd6980

I have a routine in which the following code:

is clearing both the cell contents and the formats.

There is event handling routine in the Sheet that triggers on any change in column C, but I have disabled this entire routine with no effect.

There is no conditional formatting, nor anything else particularly unusual about the worksheet, yet I can't replicate this in any other worksheet.


I've started having a nasty problem with Excel about 2 weeks ago. I have a lot of macros that update data in spreadsheets, I always put in a Clear or ClearContents statement to get rid of the old stuff before pasting new data. When I select a lot of cells (say "Range("A2:Z60000").Clear), it has always taken less than a second to do this. Now suddenly, it just hangs, and Excel uses 100% CPU capacity. I've left it for hours, but no use.

The same happens when I select a lot of data and press delete, so the problem is not in VB. It's not just one file, it's anyhing I try it on.

I have re-installed Excel, then I deleted my .xlb file and re-installed Excel, nothing works. I use Excel 2003 SP2. Please help!

I have several worksheets which I would like to clear all contents and thus
it would be ready for data input next time. I tried Clear or ClearContents
but some sheets do not clean up, any idea please ?

I have a routine that highlights a range and removes any entries in
that range. For some reason the ClearContents command halts processing
AFTER it has performed the removal of entries. If I comment that line
out the routine runs as expected.

The code is
Sub PoP5()
If Sheets("Intro").Range("h16").Value = False Then
Worksheets("Labor").Unprotect Password:="xxx1"
Selection.EntireColumn.Hidden = True

With the commented out, the columns hide and processing continues;
with the ClearContents active the entries are removed but processing
stops. I tried breaking that line into two, comme ca:
Selection.ClearContents no avail, the same thing happens.

Anyone have thoughts for the cause, or a workaround?


I have conditional formatting set up on a column of data so that the cell at
the top of the column (A1) is white. All the other cells below are yellow.
This is their normal formatting status.

A user puts some information in cell A1. Conditional formatting in cell A2
(formula is =A1"") now changes cell A2 to white. This acts as a kind of a
prompt, showing the user where to put the next bit of data. And when the
user puts the data in A2, then A3 goes white (using =A2"") and so on. It
works nicely. Same thing all the way down that column.

I can clear those items out using the delete key, or using a bit of code
like range("a1:a5").clearcontents. It works fine and they all turn back to

If I go into a different workbook, and type 3 or 4 data items in a column,
then I copy those back into my sheet, and paste them using pastespecial >
values, it all seems to have worked well. The cells are white, and the next
cell below is also white. However, now if I press the delete key, or use my
clearcontents program, they don't change back to yellow. Excel thinks that
the cells are not empty any more, even though I thought I was only pasting
values in there.

What is going on?



You can loop through all the dependent cells and clear their contents.
Here's an example that uses the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

'Stop this sub from running based on changes made here
Application.EnableEvents = False

Dim cell As Range
Dim DepRng As Range

'Changed cell is in A1:I1
If Not Intersect(Target, Me.Range("A1:I1")) Is Nothing Then

'Change cell was deleted
If IsEmpty(Target) Then

'Set up error handling in case their is no dependents
On Error Resume Next

Set DepRng = Target.Dependents

'If there are dependent cells
If Err.Number = 0 Then

'Loop through them and clear them
For Each cell In DepRng.Cells
Next cell
End If
On Error GoTo 0
End If
End If

Application.EnableEvents = True

End Sub

Dick Kusleika
MVP - Excel
Post all replies to the newsgroup.

"Vinnie" > wrote in message
> How do I clear the contents from a range of cells, which are dependent on
another cell being populated. For example, If I enter a Value in cell A1,
A5:A10 are populated with data according to the value entered in A1. A value
in B1 populates data in B5:B10, C1 populates data in C5:C10 etc.....
> My question is if I clear either A1,B1 or C1, I also want to clear the
corresponding data in A5:A10, B5:B10, C5:C10 etc
> If I only clear A1 & C1 then contents from A range & C range should only
clear. Any ideas would be appreciated.


I use ClearContents to reset data before take my calculation on a hidden
worksheet. however, the code is get same error.

Private Sub CommandButton1_Click()
End Sub

Run-time error '1004':

ClearContents method of Range class failed

Can someone help me with this?

When I manipulate some data on worksheet X with a button fired macro, the
change event procedure of forms combobox on another worksheet (that refers
to data on worksheet X via a range name) fires that has the following code:

ThisWorkbook.Sheets("Mapping").Range("subd").Clear Contents

This code is raising an error, 1004 "ClearContents method of the Range class
failed". The range spec is correct, this code runs fine and as expected
when the combo is changed.

Why won't the above code run when the event is triggered by another macro?

I have tried to set the EnableEvents property of the XLApp to false when
macro1 runs, but the event is still fired, and the error still occurs.

Any ideas?

Thanks! JDM

Is there any way to speed up this macro? The macro clears specific columns in a row when you click anywhere on the row and then hit the command button. It clears the first range and 2nd range in 2 distinct steps, and takes up to 3 seconds. I'm brand new at VB and wrote this myself, but would like help. Thanks!

Private Sub CommandButton1_Click()
Const pw As String = "blondie"
Dim Rng As Range, Rng2 As Range
Set Rng = Range("B17:J116")
Set Rng2 = Range("L17:N116")
ActiveSheet.Unprotect pw
Intersect(Selection, Rng).ClearContents
Intersect(Selection, Rng2).ClearContents
ActiveSheet.Protect pw, AllowSorting:=True, AllowFiltering:=True
End Sub