2 ways for “ClearContents” on VBA Excel, but 1 work fine. Why? [duplicate]

by

It is because you haven’t qualified Cells(1, 1) with a worksheet object, and the same holds true for Cells(10, 2). For the code to work, it should look something like this:

Dim ws As Worksheet

Set ws = Sheets("SheetName")
Range(ws.Cells(1, 1), ws.Cells(10, 2)).ClearContents

Alternately:

With Sheets("SheetName")
    Range(.Cells(1, 1), .Cells(10, 2)).ClearContents
End With

EDIT: The Range object will inherit the worksheet from the Cells objects when the code is run from a standard module or userform. If you are running the code from a worksheet code module, you will need to qualify Range also, like so:

ws.Range(ws.Cells(1, 1), ws.Cells(10, 2)).ClearContents

or

With Sheets("SheetName")
    .Range(.Cells(1, 1), .Cells(10, 2)).ClearContents
End With

More Related Contents:

  1. VBA comma separated cells, needt last entry only
  2. How can I find last row that contains data in a specific column?
  3. Open Excel file for reading with VBA without display
  4. Wait for shell command to complete [duplicate]
  5. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  6. How to add a named sheet at the end of all Excel sheets?
  7. Find last used cell in Excel VBA
  8. VBA general way for pulling data out of SAP [closed]
  9. Does Dir() make any guarantee on the order of files returned?
  10. Difference between ‘on error goto 0’ and ‘on error goto -1’ — VBA
  11. Excel: How to check if a cell is empty with VBA? [duplicate]
  12. ShowAllData method of Worksheet class failed
  13. Assign keyboard shortcut to run procedure
  14. Uncheck all checkboxes across entire workbook via CommandButton
  15. return csv file as recordset
  16. VBA workbooks.Close without being prompted to if the user wants to save?
  17. Excel macro -Split comma separated entries to new rows [duplicate]
  18. Get text from clipboard using GetText – avoid error on empty clipboard
  19. Copy a selected range to another worksheet
  20. Benchmarking VBA Code
  21. Excel macro – paste only non empty cells from one sheet to another
  22. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  23. Display milliseconds in Excel
  24. VBA editor auto-deletes spaces at the ends of lines
  25. Application.Quit command not closing the entire Excel Application
  26. Create hyperlink to another sheet
  27. Trying to open the workbook in separate instance
  28. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  29. Access files with long paths (over 260)
  30. How to highlight selected text within excel

Leave a Comment