CODEWITHSUNDEEP
vbaexcel
icobes
icobes

Reputation: 187

Clear Contents of a Column

How would I clear the contents of a column from cell A3 to cell __ where __ represents the last entry in the column (assuming there are no empty spaces between entries).

Thanks for the help.

Upvotes: 5

Views: 74336

Answers (4)

Banjoe
Banjoe

Reputation: 1768

range("A3", Range("A" & Columns("A").SpecialCells(xlCellTypeLastCell).Row)).Delete

That will delete A3 through the last cell in column A, regardless of any blanks in the column.

range("A3", range("A3").End(xlDown)).Delete

That will delete from A3 down to the first blank cell after A3 in column A.

EDIT: Fixed the first code snippet so it only deletes cells in column A.

Upvotes: 10

Michael Easterbrook
Michael Easterbrook

Reputation: 71

I have had good results with this:

Set tbl = ActiveSheet.ListObjects("Table_Name")
Count = tbl.DataBodyRange.Rows.Count

Range("AC2:AC" + CStr(Count)).Select
Selection.ClearContents

Upvotes: 0

kodi1911
kodi1911

Reputation: 722

I would use a vbNullString, because it's slightly faster and works efficently on huge amount of data worksheets.

Paste 'nothing' from A3 to the first blank cell in column A:

Range(Cells(1,3), Cells(Range("A3").End(xlDown).Row,1)).Value = vbNullString

Paste 'nothing' from A3 to the last cell in column A:

Range(Cells(1,3), Cells(Range("A3").SpecialCells(xlTypeLastCell),1)).Value = vbNullString

Upvotes: 2

Gaijinhunter
Gaijinhunter

Reputation: 14685

Range("A3", Range("A3").End(xlDown)).Clear

Using .Delete will actually delete the cells, shifting up any cells that might appear after this list (separated by a blank cell). If you just want to clear the contents, .Clear is a good way to go.

Upvotes: 11

Related Questions