CODEWITHSUNDEEP
excelvba
Charles0110
Charles0110

Reputation: 11

Delete all data but one column in VBA

Sorry for this basic question.

I am trying to delete data from a worksheet, but I actually want to keep all the data that is in column A. I have worked out how to clear all the rows whilst keeping the header but can't find a way to save the data in column A.

Does anyone know how to do this? 

    With Worksheets("Data")
     .Rows("2:" & .UsedRange.Count).Delete
    End With

Upvotes: 1

Views: 98

Answers (2)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

another way to achieve is as below

Sub test()
    lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
    lastcolumn = Cells.SpecialCells(xlCellTypeLastCell).Column
    Range("B2", Cells(lastrow, lastcolumn)).Delete
End Sub

Upvotes: 2

teylyn
teylyn

Reputation: 35915

The .UsedRange.Count will return a count of all cells in the used range, not just the rows.

If I understand correctly, you want to delete everything from B2 to the end of the used range. You can do that like this:

With Worksheets("Data")
    .Range("B2", Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).ClearContents
End With

Upvotes: 2

Related Questions