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.
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.
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?
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
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
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!