Clear contents of cells in VBA using column refere

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

I found this an easy way of cleaning in a shape between the desired row and column. I am not sure if this is what you are looking for. Hope it helps.

Sub sbClearCellsOnlyData()
Range("A1:C10").ClearContents
End Sub

Answer 2

I just came up with this very simple method of clearing an entire sheet.

Sub ClearThisSheet()

ActiveSheet.UsedRange.ClearContents

End Sub

Answer 3

The issue is not with the with statement, it is on the Range function, it doesn't accept the absolute cell value.. it should be like Range("A4:B100").. you can refer the following thread for reference..

following code should work.. Convert cells(1,1) into "A1" and vice versa

LastColData = Sheets(WSNAME).Range("A4").End(xlToRight).Column
            LastRowData = Sheets(WSNAME).Range("A4").End(xlDown).Row
            Rng = "A4:" & Sheets(WSNAME).Cells(LastRowData, LastColData).Address(RowAbsolute:=False, ColumnAbsolute:=False)

 Worksheets(WSNAME).Range(Rng).ClearContents

Answer 4

To clear all rows that have data I use two variables like this. I like this because you can adjust it to a certain range of columns if you need to. Dim CRow As Integer Dim LastRow As Integer

CRow = 1
LastRow = Cells(Rows.Count, 3).End(xlUp).Row

Do Until CRow = LastRow + 1
    Cells(CRow, 1).Value = Empty
    Cells(CRow, 2).Value = Empty
    Cells(CRow, 3).Value = Empty
    Cells(CRow, 4).Value = Empty
    CRow = CRow + 1
Loop

Answer 5

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 6

You need a With statement prior to this. Or make the .Cells into Cells