Clear contents of cells in VBA using column reference

Question

I am trying to get a piece of code to clear the data in some cells, using the column references. I am using the following code:

Worksheets(sheetname).Range(.Cells(2, LastColData), .Cells(LastRowData, LastColData)).ClearContents

To do this, however I am getting an error at the first .Cells section, why is this?

Answer 1

You can access entire column as a range using the Worksheet.Columns object

Something like:

Worksheets(sheetname).Columns(1).ClearContents 

should clear contents of A column

There is also the Worksheet.Rows object if you need to do something similar for rows

---

The error you are receiving is likely due to a missing with block.

You can read about with blocks here: Microsoft Help

Answer 2

For anyone like me who came across this and needs a solution that doesn't clear headers, here is the one liner that works for me:

ActiveSheet.Range("A3:A" & Range("A3").End(xlDown).Row).ClearContents

Starts on the third row - change to your liking.