Clear all contents of a column except it's header

Question

I need to clear all contents of a column except it's header.
I used the below code but I got Run-time error '1004': Method 'Range' of object '_Worksheet' failed

Sub clear_contents_of_column_except_header()

    Dim sh As Worksheet, lastcolumn As Long
    Set sh = ActiveSheet
    lastcolumn = 10

    sh.Range(Cells(2, lastcolumn).End(xlDown).Row).ClearContents

End Sub

Essentially you are doing `sh.Range(42).ClearContents`. What would it mean to pass 42 to `sh.Range()`? — GSerg, Mar 12 '23 at 13:12
@GSerg , I need to clear all cells on column 10 ,except it's header — Soma, Mar 12 '23 at 13:26
Try `sh.Range(sh.Cells(2, lastcolumn), sh.Cells(2, lastcolumn).End(xlDown)).ClearContents` you you can simply try `sh.Range("J2:J" & sh.Rows.count).ClearContents` — Siddharth Rout, Mar 12 '23 at 13:40
@Siddharth Rout LastColumn is variable ,so I am using `cells` .I tried first option and It did not work correctly — Soma, Mar 12 '23 at 13:54
`I tried first option and It did not work correctly` Could you please explain what do you mean? You can also try `sh.Range(sh.Cells(2, lastcolumn), sh.Cells(sh.Rows.Count, lastcolumn)).ClearContents` — Siddharth Rout, Mar 12 '23 at 14:03
@@Siddharth Rout , the values on lastcolumn is not continuous (there are a blanks in between) ,So your last variant works correctly — Soma, Mar 12 '23 at 14:08
I had a suspicion about it. And hence I was posting an answer with 2 alternatives. — Siddharth Rout, Mar 12 '23 at 14:10

Siddharth Rout · Accepted Answer · 2023-03-12T14:14:25.730

@Siddharth Rout LastColumn is variable ,so I am using cells. – Soma 14 mins ago

If LastColumn is a variable then here are two ways to achieve what you want

With sh
    .Range(.Cells(2, LastColumn), .Cells(.Rows.Count, LastColumn)).ClearContents
End With

You can also use an alternate method.

Dim LastCol As String

With sh
    LastCol = Split(.Cells(, LastColumn).Address, "$")(1)
    '~~> The below is similar to 
    '~~> sh.Range("J2:J" & sh.Rows.count).ClearContents which is nothing but
    '~~> sh.Range("J" & "2:" & "J" & sh.Rows.count).ClearContents
    .Range(LastCol & "2:" & LastCol & .Rows.Count).ClearContents
End With

One important note. Fully qualify the objects (Ex: Cells, Rows etc.) like I have done above (Notice the DOT before the object). You may want to read about it in Why does Range work, but not Cells?

It works and it toke 0.2 seconds to finish (4k cells) , I thought it can be faster — Soma, Mar 12 '23 at 14:29

Clear all contents of a column except it's header

1 Answers1