Hello,

This forum is extremely helpful so thank you all in advance. I have a code that is meant to erase a row based on "True" in column Y and move the contents to another sheet (for archiving purposes). This code currently just clears the row on the "Main" sheet- erasing everything in the row. I was wondering the best way to modify this to just erase (not cut or delete) cells A:X and then shift the next cells up to replace the A:X that was cleared. This is so there is not a blank unformatted row in the middle of my dataset.

I have tried to change it but I seem to be stuck at the 
Rows(r.Row).Clear
area. I tried to change that to cells(r.cells) but it doesnt seem to work to just erase A:X and im not entirely sure how to define the part where it moves the next row up? Any help would be greatly appreciated. 

Sub Rectangle1_Click()

Dim rng As Range

Sheets("Main").Select

Application.ScreenUpdating = False

destRng = Sheets("Archive").Cells(Rows.Count, 2).End(xlUp).Row + 1

Set r = Columns(25).Find("True", LookIn:=xlValues, Lookat:=xlWhole)

If r = "True" Then

Target = Range(Cells(r.Row, 1), Cells(r.Row, 23)).Address

Range(Target).Copy Destination:=Sheets("Archive").Range("A" & destRng)

Sheets("Archive").Columns("A:X").AutoFit

Rows(r.Row).Clear

Exit Sub

        
End If

Application.ScreenUpdating = True

MsgBox "All files have been Archived."