16

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?

dojogeorge
  • 1,674
  • 3
  • 25
  • 35

8 Answers8

31

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

Sam
  • 7,245
  • 3
  • 25
  • 37
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.

Eric Sloan
  • 169
  • 1
  • 5
  • 1
    This seems to only go to the End of the page, not all the way down the column – tofutim Aug 02 '18 at 17:19
  • For me this only goes until the first empty cell in the row. Would be great to instead have a quick way to go really until the last non-empty cell in the entire row. – FlorianH Apr 11 '20 at 15:08
1

As Gary's Student mentioned, you would need to remove the dot before Cells to make the code work as you originally wrote it. I can't be sure, since you only included the one line of code, but the error you got when you deleted the dots might have something to do with how you defined your variables.

I ran your line of code with the variables defined as integers and it worked:

Sub TestClearLastColumn()

    Dim LastColData As Long
        Set LastColData = Range("A1").End(xlToRight).Column

    Dim LastRowData As Long
        Set LastRowData = Range("A1").End(xlDown).Row

    Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData)).ClearContents

End Sub

I don't think a With statement is appropriate to the line of code you shared, but if you were to use one, the With would be at the start of the line that defines the object you are manipulating. Here is your code rewritten using an unnecessary With statement:

With Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData))
    .ClearContents
End With

With statements are designed to save you from retyping code and to make your coding easier to read. It becomes useful and appropriate if you do more than one thing with an object. For example, if you wanted to also turn the column red and add a thick black border, you might use a With statement like this:

With Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData))
    .ClearContents
    .Interior.Color = vbRed
    .BorderAround Color:=vbBlack, Weight:=xlThick
End With

Otherwise you would have to declare the range for each action or property, like this:

    Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData)).ClearContents
    Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData)).Interior.Color = vbRed
    Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData)).BorderAround Color:=vbBlack, Weight:=xlThick

I hope this gives you a sense for why Gary's Student believed the compiler might be expecting a With (even though it was inappropriate) and how and when a With can be useful in your code.

Instant Breakfast
  • 1,383
  • 2
  • 14
  • 28
  • 2
    You need to define the variables as Long, otherwise you will have issues past row 32767 – Sam Oct 28 '13 at 07:50
1

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

Sub ClearThisSheet()

ActiveSheet.UsedRange.ClearContents

End Sub
Don Leverton
  • 19
  • 5
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
Zapata
  • 133
  • 1
  • 5
  • 20
0

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

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • I tried removing the . before each of the Cells to get this `Worksheets(sheetname).Range(Cells(2, LastColData), Cells(LastRowData, LastColData)).ClearContents` And I get the error 'Application Defined error or object defined error'. When using a **WITH** where should that go? – dojogeorge Oct 25 '13 at 15:34
0

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
Community
  • 1
  • 1
Rav
  • 1
  • 2
0

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
Kitten
  • 1