Question posted 2013 · +4 upvotes
Good evening friends:
I have in mind 2 ways for clearing a content in a defined range of cells of a VBA project (in MS Excel):
Worksheets("SheetName").Range("A1:B10").ClearContentsWorksheets("SheetName").Range(Cells(1, 1), Cells(10, 2)).ClearContents
The problem is that the second way show me an error ‘1004‘ when I’m not watching the current Worksheet “SheetName” (in other words, when I haven’t “SheetName” as ActiveSheet).
The first way work flawlessly in any situation.
Why does this happen? How can I use the “Second way” without this bug?
Accepted answer +8 upvotes
That is because you are not fully qualifying your cells object. Try this
With Worksheets("SheetName")
.Range(.Cells(1, 1), .Cells(10, 2)).ClearContents
End With
Notice the DOT before Cells?
Excel VBA objects referenced (4)
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
excel-vba solutions on this site
.