Macro does not clear the contents!

Macro does not clear the contents!

Didn't find your answer?

I have a worksheet which has an income and expense analysis for 12 months. I would like to re-use it by keeping the formula and format intact, but clear the contents of each cell. So, if a cell does not have a formula, but has a text or number, it should be clear after I run the macro.

I came up with a simple macro, but did not seem to work, so I have posted the question on the code forum site. I have now two macros in which one of the macro does not seem to have a syntax error, but does not do the job, i.e. it loops through each sheet, but does not clear the contents. The other macro has a syntax error.

Could someone please spot what is wrong with these two codes?

Cheers

Sub clearcontents()

    Dim Rng As Range, oCell As Range

    Set Rng = Range("A2:A100")

    For sh = 1 To Worksheets.Count '+for each sheet

        Sheets(sh).Activate '+

        For Each oCell In Rng

            If oCell.HasFormula Then

                Exit Sub

            Else

                oCell.clearcontents

            End If

        Next

    Next sh '+

End Sub

Sub snb()

  For Each sh In Sheets

    sh.Range("A2:A100").SpecialCells(2).clearcontents          ‘ a line which shows an error

  Next sh

End Sub

Replies (9)

Please login or register to join the discussion.

avatar
By leestevens
19th Mar 2012 10:44

Try this

For the first macro there are a few errors and/or things to consider.

Firstly the line

Set Rng = Range("A2:A100")

This means the macro is just looking at column A between row 2 and 100 so when working properly would only ever clear cells in that range.  You may want to change this to cover your situation. e.g. Range("A1:Z999") although be careful not to use too big a range as the macro will take a long time to run.

 

The next line to look at is

If oCell.HasFormula Then

Change this to read     If oCell.Cells.HasFormula Then

 

and similarly the line

oCell.clearcontents

Change this to read oCell.Cells.ClearContents

 

Hope that helps.  I didn't look at the second listing as the first one seems to work with the amendments above.

Thanks (0)
By George Attazder
19th Mar 2012 12:57

Couldn't you just...

... save a blank version of the spreadsheet as a template, and just use the template each time you want to recreate it?

Thanks (0)
avatar
By Cantona1
19th Mar 2012 13:37

Macro does not clear the contents!

leestevens: Thank you for spotting the error. Yes, the range was wrong.

I have tried this ameded code, but still does not work.

 

Sub clearcontents1()
    Dim Rng As Range
    Dim oCell As Range
    Set Rng = Range("A3:R100")
    For sh = 1 To Worksheets.Count '+for each sheet
        Sheets(sh).Activate '+
        For Each oCell In Rng
           If oCell.Cells.HasFormula Then
           Exit Sub
           Else
           oCell.Cells.clearcontents
            End If
        Next
    Next sh '+
End Sub

 

Thanks (0)
avatar
By ACDWebb
19th Mar 2012 14:27

Is that not exiting

the first time it hits a cell with a formula?

Would this not do it?

Sub clearcontents1()
    Dim Rng As Range
    Dim oCell As Range
    Dim sh
    Set Rng = Range("A3:R100")
    For sh = 1 To Worksheets.Count '+for each sheet
        Sheets(sh).Activate '+
        For Each oCell In Rng
           If Not oCell.Cells.HasFormula Then oCell.Cells.ClearContents
        Next
    Next sh '+
End Sub

 

Thanks (0)
avatar
By Cantona1
19th Mar 2012 14:41

Macro does not clear the contents!

ACDWebb,

As I always thanks for your help! Since you are one of the few who are excel expert in this forum, I was expecting a response from you.

Still, for some unknown reason, it is not working. I can see it loops through each sheet, but does not clear the cell contents.

 

Thanks (0)
avatar
By ACDWebb
19th Mar 2012 14:47

Blush. I REALLY wouldn't go that far!!

being a self taught amateur - as is probably obvious to others better versed than I!!

 

Have you tried stepping through the macro and watching say If Not oCell.Cells.HasFormula setting it to stop when true so you can look at what it is saying is in the cell

Thanks (0)
By George Attazder
19th Mar 2012 16:57

Try this

Sub Blanker()
    Dim Rng As Range, oCell As Range, oSheet As Worksheet
    For Each oSheet In Worksheets
        Set Rng = oSheet.Range("A3:R100")
        For Each oCell In Rng
            If Not oCell.HasFormula Then oCell.ClearContents
        Next
    Next
End Sub

The reference to oCell.Cells is erroneous in my view as it then needs to be qualified with a selection in parenthesis to return another range within the one cell range.

I still think using a template would be a better approach though!

Thanks (0)
avatar
By Cantona1
19th Mar 2012 14:58

Macro does not clear the contents!

Sorry All,

I think I found out why the macro is not working. I have created a new dummy sheet and put some figures on some cells for test. The macro works on this sample sheet.

Each sheet had grouped rows and columns. When I removed these- by ungrouping on the data menu, the macto works on all sheets now.

So, may be the macro was fine, but there was a problem with the formats of my worksheet. But still I do not know why it does not clear cells which are grouped by rows, or columns.

Initially, the sheets were set as table, but later converted them in to ranges, so I am wondering if the macro does not recognise each separate cell.

Thanks (0)
avatar
By Cantona1
19th Mar 2012 16:50

Macro does not clear the contents!

 

Hi All,

Just to update you with  my macro. George marco is great!

I have not changed any format in the sheets. Just copied the macro and run it.

I am not only a self thought like ACDWebb, but also a starter.

George: You are right! I could have done it using a template, but I am learning macro and this is the best way to learn it. I have picked few lessons today. For example, as ACDWebb have spotted, there was an error with  some lines of my macro. If a cell finds a formula, it exit the sub, so my macro was not looking at each cell if the first cell happen to have  a formula.

Thanks all of you for your contributions.

Thanks (0)