CODEWITHSUNDEEP
vba
Pierre Bonaparte
Pierre Bonaparte

Reputation: 633

Clear specific range below the lastRow (do not remove rows)

Can you advise how to clear specific range below the lastRow of column A ?

So, my current code "removes the rows"

.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(.UsedRange.Rows.Count, 1).EntireRow.Delete

What I need instead is to [clear] a rectangle of data below the lastRow in column A. So the code needs to go to the cell that is below the last populated cell in column A and clear the rectangle of B:V + 10 levels below (I dont want to remove rows).

Thanks

Upvotes: 0

Views: 1010

Answers (2)

Miguel_Ryu
Miguel_Ryu

Reputation: 1418

Try this.

'find last populated cell in column A
Set last = .Range("A:A").Find("*", Cells(1, 1), searchdirection:=xlPrevious)
'range last.row + 1 to last.row + 1 + 10 from columns B:V clear
.Range(.Cells(last.Row + 1, 2), .Cells(last.Row + 11, 22)).Clear

Which find the row and clears a defined range

Upvotes: 0

FunThomas
FunThomas

Reputation: 29171

To remove the content of cells, use the Range method clearContents.
To remove also the formatting, use the method clear. 

Const NumberOfRowsToBeCleared = 10
Dim r As Range, lastRow As Long, maxRow As Long, ws As Worksheet
Set ws = ActiveSheet    ' Assign to the worksheet you want to deal with
With ws
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    ' This version clears 10 rows
    Set r = .Range("B" & lastRow + 1 & ":V" & lastRow + NumberOfRowsToBeCleared+1)
    ' Alternative way to define the range
    Set r = .Range(.Cells(lastRow + 1, 2), .Cells(lastRow + NumberOfRowsToBeCleared+1, 22))

    ' This version clears until the end of the sheet
    maxRow = .UsedRange.Rows.Count
    Set r = .Range("B" & lastRow + 1 & ":V" & maxRow + 11)
    ' Again alternative way to define the range
    Set r = .Range(.Cells(lastRow + 1, 2), .Cells(maxRow, 22))

    r.ClearContents  ' Clear contents
    '  r.Clear  ' Clear contents and formatting

End With

Upvotes: 1

Related Questions