CODEWITHSUNDEEP
vbaexcelexcel-2013
SmallFries BigGuys
SmallFries BigGuys

Reputation: 171

Clear Used Range Starting With Specific Cell

I want to clear the used range of Sheet1, but starting with A8 and down. I want to leave the data in A7 and up in-tact, but anything below that range, clear out. I tried this syntax, but it is not going all the way to the end of the workbook ("used range"). And yes, the last cell of the used range is before A65536

For LastRow = 2 To Worksheets("Sheet1").Range("A65536").End(xlUp).Row
  Next LastRow
Range("A" & LastRow).ClearContents

How can I clear all cells with data from row A8 to end of used range?

Upvotes: 1

Views: 4080

Answers (3)

brettdj
brettdj

Reputation: 55702

Or version agnostic (which caters for the version of Excel you have, it is Excel-2003 that has 2^16 or 65536 rows)

Range([a8], Cells(Rows.Count, "A")).EntireRow.ClearContents

Upvotes: 0

Bob Goblin
Bob Goblin

Reputation: 1269

You are close try the below - this way you do not have to specify the "end range". It will delete everything below row 8

With Sheets("Sheet1")
    .Rows(8 & ":" & .Rows.Count).Delete
End With

Upvotes: 1

Comintern
Comintern

Reputation: 22205

Your code (other than doing nothing in the loop) only builds an address for one cell. Just build a Range like 8:42 or whatever the last row is and clear that:

With Worksheets("Sheet1")
    .Range("8:" & .Range("A65536").End(xlUp).Row).ClearContents
End With

Upvotes: 0

Related Questions