VBA Excel: Clear the entire col expect A1

[harky]

Need some help,

How to Clear the entire col expect A1 ? The column contain data & empy so i want to clear the whole column except a1

btw, i had

column A
column C
column D
column E
column I
column J
column K

need to clear

 

[Akuini]

Try:

Sub try1()
Dim tx
tx = Range("A1")
Range("A:A, C:E, I:K").ClearContents
Range("A1") = tx
End Sub

 

[harky]

Thanks how to add a ws?

Set ws = ThisWorkbook.Worksheets("path")

Try:

Sub try1()
Dim tx
tx = Range("A1")
Range("A:A, C:E, I:K").ClearContents
Range("A1") = tx
End Sub

 

[Akuini]

Try:

Set ws = ThisWorkbook.Worksheets("path")
tx = ws.Range("A1")
ws.Range("A:A, C:E, I:K").ClearContents
ws.Range("A1") = tx

Or

Set ws = ThisWorkbook.Worksheets("path")
With ws
tx = .Range("A1")
.Range("A:A, C:E, I:K").ClearContents
.Range("A1") = tx
End With

Or without ws

With ThisWorkbook.Worksheets("path")
tx = .Range("A1")
.Range("A:A, C:E, I:K").ClearContents
.Range("A1") = tx
End With

 

[harky]

Thanks!!!

Try:

Set ws = ThisWorkbook.Worksheets("path")
tx = ws.Range("A1")
ws.Range("A:A, C:E, I:K").ClearContents
ws.Range("A1") = tx

Or

Set ws = ThisWorkbook.Worksheets("path")
With ws
tx = .Range("A1")
.Range("A:A, C:E, I:K").ClearContents
.Range("A1") = tx
End With

Or without ws

With ThisWorkbook.Worksheets("path")
tx = .Range("A1")
.Range("A:A, C:E, I:K").ClearContents
.Range("A1") = tx
End With

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[harky]

Hi,
I think i make a mistake..

I dont want it to clear ROW 1 (which is the header)

Try:

Set ws = ThisWorkbook.Worksheets("path")
tx = ws.Range("A1")
ws.Range("A:A, C:E, I:K").ClearContents
ws.Range("A1") = tx

Or

Set ws = ThisWorkbook.Worksheets("path")
With ws
tx = .Range("A1")
.Range("A:A, C:E, I:K").ClearContents
.Range("A1") = tx
End With

Or without ws

With ThisWorkbook.Worksheets("path")
tx = .Range("A1")
.Range("A:A, C:E, I:K").ClearContents
.Range("A1") = tx
End With

 

[Michael M]

They work fine for me...
are you running the entire code or just stepping through line by line ?
Line by line will initially clear the entire column, but replaces A1 when it is finished.

 

[Rick Rothstein]

What about this single line of code...

Intersect(Range("2:" & Rows.Count), Range("A:A, C:E, I:K")).ClearContents

You can add worksheet and workbook references if you do not always want the data's worksheet always active.

 

[harky]

oh..... cant replaces a1 because is a header

They work fine for me...
are you running the entire code or just stepping through line by line ?
Line by line will initially clear the entire column, but replaces A1 when it is finished.