Difference between .Delete and .Clear in Excel VBA?

Question

What is the difference between Worksheets(1).Cells.Delete and Worksheets(1).Cells.Clear?

I'm asking this because I've always used .Clear to clear my Worksheet content, but in my previous post I've discovered that Worksheets(1).Cells.Delete not only delete my Worksheet content but it also set Columns on their default width!

Can anyone explain me the difference? And also can I give a range to .Delete ?

Answer 1

Range.Delete actually delete respective range and shifts/move cells accordingly thus changing the structure of the worksheet. It's same if you select cells and right-click to Delete selection. You get asked how do you want to move cells.

Range.Clear will just clear the content and formatting, but will not delete cells and not change the structure of the worksheet.

There is also Range.ClearContents that will clear just content, but preserve formatting, Range.ClearFormats to just clear formatting of the cells and Range.ClearComments, Range.ClearHyperlinks, Range.ClearNotes and Range.ClearOutline