Free Microsoft Excel 2013 Quick Reference

Clear sheet contents before continuing with macro

Hi people,

Im looking to ensure the sheet contents are clear before continuing with the macro routine. At the moment however, the macro runs I see the data flash up and then off. So it appears the data is being cleared as soon as it loads. Current code is:

Sub CollateData()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Set sht = Worksheets("AnalysisDump")
With sht.Cells
End With

    If ws.Name <> "mainmenu" And ws.Name <> "foldernamedump" And ws.Name <> "AnalysisDump" Then
        With ws
            Sheets("AnalysisDump").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            Sheets("AnalysisDump").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        End With
    End If
Next ws
Application.CutCopyMode = False
End Sub

Post your answer or comment

comments powered by Disqus
Starting with something in the system clipboard, a macro I am trying to use pastes the contents of the clipboard to an area on my worksheet. The paste is successful unless I first clear the contents of the area. The clear apparently deletes the contents of the clipboard and the paste gives an error. The commands I am using in the macro to do the paste:

Range (“A1:D200”).select


Is there a command that I can use that will clear the contents before doing a paste but still allow successful completion of the paste?

Hi there,

I'm new here and not very familiair with VBA.
I've searched on the internet for many days but can't find to solve my problem.

I've an Excel Workbook with several sheets.
A sheet named "Vragenlijst" contains 20 questions. Each question has 4 option buttons (Control Toolbox) in a group.

In the second sheet named "Controlevelden" the values of the 20 aswers are registered in colom B2 to B21.
In B27 i wrote a formula: =COUNT.IF(B2:B21;">0")>=20

After the questions are aswered there will be a summary of textual answers on the sheet named "Rapportage".

I have 1 command button proceeding all the actions neccesary. After pushing the button
a macro takes a picture (with 'camera'-option in Excel) of this summary and places it on a special place on sheet "Vragenlijst"

A MsgBox is giving me a box with "You didn't anwer 1 (or more) questions" when a question has not been answers (option button not chosen). When all the question are answered: "You answered all the question".

The problem is that after "You didn't asnwer 1 (or more) questions" it continues with the next macro in VBA.
I want to go to the not answered question before going further with the VBA command.

The command button has this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ScrollArea = "A1:F132"
End Sub

Private Sub ComboBox1_Change()

End Sub

Private Sub commandbutton1_click()

End Sub
the module (sub) 'checkvragen' has this code:
Option Explicit

Sub checkvragen()
    Dim rRange As Range
    Dim oCell As Variant
    Dim Counter As Long
    Dim Cancel As Integer
    Counter = 0
    Set rRange = Sheets("Controlevelden").Range("B2:B21")
    For Each oCell In rRange
        If oCell.Value = 0 Then
            Counter = Counter + 1
        End If
    If Counter <> 0 Then
        MsgBox "U heeft  " & Counter & "  vraag/vragen nog niet beantwoord."
        Else: MsgBox "U heeft alle vragen beantwoord."
        Cancel = True
        End If
End Sub
Can anyone help me please?


I 've found some fine piece of code to import Exceldata into Access, but I still have one problem: Excel places the new data under the already present data. However, I do not want to append the Exceldata, but to overwrite (part of) the existing data.

How can I make Excel clear the contents of one or more Access columns (before subsequently importing new data into these cells)?

Thanks in advance,


I want to clear the contents of cells with no color. When I run the following code, I get "Cannot change part of a merged cell." error. Any suggestion would be greatly appreciated. Thank you.

For Each c In ActiveSheet.UsedRange 
    If c.Interior.ColorIndex = xlNone Then c.ClearContents 
Next c 

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

I have a macro that one of the things it does is set the page size to

This works great on HP printers (that support that size of course) but
there are some users that use a konica printer that uses tabloid
instead of 11x17. (same size, its just the printer driver recognizes
one and not the other)

to set one up would be

..PaperSize = xlPaper11x17

and for the other one it would be

..PaperSize = xlPaperTabloid

Now my question,

Is there a way that I can test first if the printer driver supports
Tabloid or 11x17??

something like

if ActiveSheet.PageSetup.PaperSize = xlPaper11x17 returns error then
ActiveSheet.PageSetup.PaperSize = xlPaperTabloid



Hi All, I was asked to repair this spreadsheet, because it all of a sudden (?) stopped working correctly. When I step through the code, all goes well with variable values, etc, until it reaches the line:

Sheets(CurrMonth).Copy Before:=Sheets(CurrMonth) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
At that point i'm not sure what it does.. I Can't seem to step futher into that line, and so it looks like it just resets/restarts the macro. My problem is that I can't get past this line, and so the macro never finishes it's job. The purpose of the macro is to copy the current sheet, and then sort the copy by the worker field, and rename the copy as something like "JAN 2006 By Tech". Here is the first page or so of code:

    Dim TotalMerch 
    Dim TotalComm 
    Dim TotalNet 
    Dim Year As String 
    Dim Month As String 
    TotalMerch = Application.Sum(Columns(7)) 
    TotalComm = Application.Sum(Columns(8)) 
    TotalNet = Application.Sum(Columns(10)) 
    Dim CurrMonth As String 
    CurrMonth = ActiveSheet.Name 'Current Month should be active sheet name
    Dim Temp 
    Temp = CurrMonth + "~" 'Temporary name of temporary sheet
    Sheets(CurrMonth).Copy Before:=Sheets(CurrMonth) 'Copies active sheet, moves before Template
    Sheets(CurrMonth + " (2)").Name = Temp 'Renames active sheet
    Dim wSht As Worksheet 
    Dim shtName As String 
    shtName = CurrMonth + " by Tech" ' e.g. June '03 by Tech
    Sheets(Sheets.Count).Copy after:=Sheets(CurrMonth) ' Copy template and move to end
    Sheets("Template (2)").Name = shtName 
    Sheets(Temp).Range("A4:K500").Sort _ 
    Key1:=Sheets(Temp).Columns(4), Order1:=xlAscending, _ 
    Key2:=Sheets(Temp).Columns(3), Order2:=xlAscending 'Sorts temporary sheet
    Dim LastRow 
    LastRow = Sheets(Temp).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 
     'Finds last row used
    TechName = UCase(Sheets(Temp).Cells(4, 4)) 
    CurrRow = 2 ' Starts importing into first available row
    Start = 2 
    Skip = 36 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help is appreciated! I can't figure out what's going on... the code looks like it is supposed to as far as I know, but it's been a while since I've done much VB scripting.


I have a difficult task at work that I can't figure out. It is very hard to explain. So this is just one part of it.

I need a macro that will go to a certain folder (example: C:Documents and Settingsu369875DesktopMacro). When I pic the file it opens copies or imports the data to a certain spot and then continues on with the macro.

I will try to explain the entire project.

What I have is a Lube sheet. It displays the tasks that people have to do. I have all of the equipment in ACCESS (there are a ton). There is a form that will allow them to select their building and the moth. Then it is imported into an excel file (problem is that there are no macros associated with it at that point). The excel at that point needs drop downs and buttons to do other things. Like format and create drop downs and then another that saves as a .CSV so another program (GEMTS) can update with what the people did for the lube.

So my "idea" (not sure if it will work) is that I have a separate "formated" excel that has a macro that will go and grab the "exported .xls from ACCESS" and then do the formatting.

Is this possible???

Please help out if you can. Even if you can do certain things. Anything will be of GREAT HELP...

thanks, Jayson

p.s. This is the only board I have this on.

HI PEOPLE, this is my very first post becasue i need help with a VBA.

I have two sheets that i am working with sheet1 and sheet2.

I need a macro that will insert multiple rows in row 8 of sheet 1.

I also need it to copy the formula from row 8 sheet1 to every new row inserted.

the number of rows inserted will depend on colum A from sheet2.

*column A contains numbers, and the amount of numbers will vary. The data starts in A11 and continues down.

So for example. if sheet 2 column A11 and A12 and A13 are the only cells that contain numbers, then the macro will add 3 lines starting in row 8 of sheet1 and it will also copy the formulas from that row to every new row inserted.

Hope that was clear. this would be my first macro. Thanks you all for this Cool website.

I have been searching on the site trying to find out how to clear a range of data. I would like to clear the contents of columns A - J beginning with row 2 to the last data entry in column A. This would be for two different tabs - Data1 and Data2.

Also - How would you write the code to delete specific cells on two different sheets. Lets say on Sheet 1 you want to clear cells a5-a8 and c9 and on sheet 2 you want to clear cells b4-b7 and b12.


I am looking for a macro to automatically clear the contents of cells in a worksheet before pasting new data on to it. Tried the BeforeDropOrPaste event but had no luck.

Thanks in advance.

Hi there,

I have a 52 sheet workbook and I would like a macro to clear the contents of
cells starting at row 2 onwards for all 52 sheets. However, some columns
contain formulas which I would like to preserve.

Any ideas would be gratefully received.

Many thanks

I know that this will be a tallish order but any advice or pointers would be gratefully appreciated. It has been many years since I last dabbled with VBA and I'm sure as hell lost over this.. I have some VBA textbooks on their way to me to help in my travails

I'm developing a workbook to hold the specification details of a series of packaging films. For each fim there are 42 specific columns an in each column there is a potential of three fields. Each record takes 3 rows so I have cut it down from 6000 rows to 900 rows in order to upload the workbook. This gives a space of 300 records of which 200 are in use. In order to index & reference this document I have added a series of consecutive numbers for each film record entry as the actual film details do not have a consecutive numbering. As a means of reference the Raw material number (RM) has also been used in a rudimentary fashion to create a search string for a VLOOKUP command (10000 is added to the RM Number then each row 0.1 is added then in the VLOOKUP this search sting is utilised again by adding 0.1). To this the main master sheet has been mostly populated (taken me for ever) and I have added two search function pages which use rudimentary VLOOKUP commands to locate & display the relevant data fields.

I have a few questions to ask on this.

1. I would like to update existing data from a separate sheet rather than going into the main data sheet, locating the record and manually overwriting the relevant field.

2. I would like to enter data for new records again from a separate sheet and populate the relevant fields without going into the main sheet an manually entering a record. When the new record is entered the record count shold automatically index to the next available record ready for future entries to be made.

3. I would like to be able to print off a generic specification document for an individual record which extracts the data from the main sheet and automatically populates the relevant fields on the specification document.

With each of the above I have sought to use manual click buttons on screen with macros associated to them. I would like to add the function of the program asking if the user is sure to proceed before the function is carried out (Yes/No/Cancel)

As mentioned earlier if anyone could give me some advice or pointers I would be grateful.

Hi there,

I've have a few macros which hide and show certain sheets in my workbook when a command button is pressed. However, for a reason which I can not figure out, the macro will suddenly stop working and a run time error occurs with the message:

Method 'Visible' of object'_Worksheet'failed I cannot figure out why this happens when moments earlier it is working. When I close the file everything is fine, it only occurs after reopening the workbook.

I am using the below code to show and hide sheets, I do have more than just the SummarySheets sub, however this is an example:


Sub SummarySheets()
Dim ws As Worksheet
MySheets = Array("Header", "RAG", "Summary by Period", "Vol Totals ")
    Call Hide_Show(MySheets)
End Sub

Function Hide_Show(MySheets)
Application.ScreenUpdating = False
For Each ws In Sheets
    X = Application.Match(ws.Name, MySheets, 0)
    If Not IsError(X) Then
    ws.Visible = True
    ws.Visible = False
    End If
Next ws
Application.ScreenUpdating = True
End Function
The error occurs on the "ws.Visible = False" line.

I also have a macro which hides all but my main sheet upon opening. A similar macro to the "SummarySheets" is one which shows ALL sheets, if I run this before running the SummarySheets subs it works fine.... the problem only occurs when I first open the workbook and click on the SummarySheets command button.

Sorry for such a long post.

Any help would be great,

Hi There

I import my data to A1, Sheet1.
Then run a macro to sort it and move it to other sheets. What I would like
to do, is do a test before the macro runs.
To Test if Column A contains the word "Found" in any sentence. if yes Stop
macro, If No continue with the macro.

Thanks in Advance



I have been working on a file and wondering if a Marco could help!
Basically, each time I need to clear some content & to delete some unnecessary datas in the C column. Finally, re-present it as a clean easy to ready report.

1. insert a new colunm after C columnn and name the new D column as " Color".
2. I will bring cell: C6 to cell: D4
3. clear the data from cell: (A5:D9)
4. delete the blank Row 10
5. Fill the data from (A5:D9) with the same data on Row 4

repeat the above 1-5 steps for the next item.

I'm attaching a sample file to show how the original file look like "before' and what is the desire "result" to be.

thanks for checking and all the help!


I'm trying to clear cell contents based on a defined name given to a set of various cells in my worksheet. The cells are not continuous, but since they're given that defined name, I don't think it matters.

The defined name is listed as "CommentsFields".
The worksheet name is listed as "QPRForm_V6"

I found a similar thread, but cannot get it to work with what I'm looking for. I'm relatively new to writing VBA/macros, so I'm not sure what to do.

Any ideas are greatly appreciated!

I would like to clear any cell that only contains “?” and/or “,”.
Take a look at the image below:

From the image above we can see that in cell D4, E1, F2, F3 and F4 the contents consist of “?” and/or “,”, so I want my macro to detect such cells and clear its content.

Do not clear contents of cell that has together with “?” and/or “s” there may be other character. For e.g. from the above image if you look at cell D2 there is other words such as jp6_7, jp6_8, so such cells should not be cleared

I would like to do this to the entire column D, E and F

The final outcome should look like the image below

I have also attached a sample worksheet,
Sheet 1 shows the initial texts
Sheet 2 contains a button where the macro should be written
Sheet 3 shows how the final outcome looks like

I have a worksheet (“A”) that is populated from a second sheet (“B”). The data on “A” changes as necessary using another Macro. Most of the data on sheet “B” is numeric and comes from various formulas and results in numbers with too may digits after the decimal point. I can format sheet “A” to turn 58.22222222 into 58.2 but when I run a Macro to clear the data I lose the formatting. I need to find a way to clear only the data and not the format, or find a way to add a mask on specific columns in the macro that copies and pastes.

Here is the Macro to clear the data.

' ClearWDRow1 Macro
' Macro recorded 6/25/2009 by Jim Ogier

Thanks Jim O


End Sub


I Need a Macro To Clear All Content (Not Delete Rows) in a Column Except a List.

List Data

Work Sheet Data

As per List England and China Not Exist. Hence This Cell Should Be Cleared. It Should come like this



Thanks in Advance.

Hello; I'm not very good with VB and I'm trying to piece together some code that I found on the web to do what I want.

Below is the code that I found on another site that indicates it will prompt a message box to the user before running a macro button.. Below that is the macro that I created and can't figure out how to rap these to macros together.

    If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo _ 
    Then Exit Sub 
     'Code goes here instead of
    MsgBox "Actions here" 
End Sub 

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

Thanks for your help...Scott

My Macro:

     ' ClearSheet Macro
     ' Macro recorded 2/15/2007 by Roundy's Inc.
    With ActiveSheet.QueryTables.Add(Connection:=Array( _ 
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:NASHFINCHExcelPbook.xls;Mode=Share
Deny Write;Extended Prope" _ 
        , _ 
        "rties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet
OLEDB:Engine Type=35" _ 
        , _ 
        ";Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet
OLEDB:New Databa" _ 
        , _ 
        "se Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale
on Compact=Fa" _ 
        , "lse;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _ 
        .CommandType = xlCmdTable 
        .CommandText = Array("EXCELPBOOK") 
        .Name = "ExcelOrdPbook_1" 
        .FieldNames = False 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlOverwriteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = False 
        .RefreshPeriod = 0 
        .PreserveColumnInfo = False 
        .SourceDataFile = "C:ExcelOrdPbook.xls" 
        .Refresh BackgroundQuery:=False 
        ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
    End With 
End Sub 

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


I am afraid im quite new to excel and have just started writing macros make life easier. I will try and explain my problem as best as I can:

I want to select B2:G11
Sort the data by column E
Then clear the contents of the cells in the rows where there is no numerical value in column E.

I have written a macro that enables me to select and sort the data but I cannot work out how to do the rest.

Some very clear instructions would be greatly appreciated.

Thank you.

Thank you in advance for any assistance that may be offered! I rarely use macros and have never played with Visual Basic, so please be patient with my ignorance!

I need to be able to clear the contents of a small range of cells from within another function such as an "IF" statement. I thought this would be easy, but in fact, has turned out otherwise. To my dismay, I found out you can't run a macro from within a function either (which would have made the problem trivial!). Using a conditional format to "hide" the cell contents is not really an option as I need to eliminate the text so certian calculations are not performed.

Any assistance would be greatly appreciated!

How to clear the contents of an entire sheet.

i want to delete the contents of the sheet1. without deleting the sheet.

I am having a probelm with macros assigned to a custom button on a custom button. I am using excel 2000.

1. I have created a macro called "Jump" in a workbook named "Test1"

2. I then created a custom toolbar and added a custom toolbar button to that toolbar.

3. Through the customize dialog box I assign the Macro "Jump" to the custom button.

Everything work fine UNTIL.

If I do a SAVE AS for the workbook "Test1" and call it something else like "Test2" and then close "Test2" and reopen "Test1" the name of the macro assigned to the custom button has change

Where as before it was simply "Jump"
now it is C:MydocumentsTest2.xls'!Jump.
Since I have the workbook "Test" open and "Test2" is closed excel now wants to open up "Test2" to access the macro assigned to that button.

I am creating a custom toolbar that will go with a excel sheet that I use as a template and will often resave it with different names.

I would like excel not to go looking in other workbooks for these macros since copies of the macros exist in the module attched to the workbook

Every time I do a Save As the assigned macro gets renamed

Does anyone have any ideas


No luck finding an answer? You could always try Google.