CODEWITHSUNDEEP
vbaexcel
dojogeorge
dojogeorge

Reputation: 1704

Clear contents of cells in VBA using column reference

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?

Upvotes: 16

Views: 321521

Answers (8)

Eric Sloan
Eric Sloan

Reputation: 189

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.

Upvotes: 3

Zapata
Zapata

Reputation: 133

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

Upvotes: 1

Rav
Rav

Reputation: 1

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

Upvotes: 0

Kitten
Kitten

Reputation: 1

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

Upvotes: 0

Don Leverton
Don Leverton

Reputation: 19

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

Sub ClearThisSheet()

ActiveSheet.UsedRange.ClearContents

End Sub

Upvotes: 1

Instant Breakfast
Instant Breakfast

Reputation: 1445

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.

Upvotes: 1

Sam
Sam

Reputation: 7303

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

Upvotes: 32

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 0

Related Questions