Q: How to clear cells after archiving?

Question

I have put together a Macro that allows me to archive Data from one sheet to another however I am having trouble having it Clear the info afterwards. The first Column contains numbers that I do not want to clear, right now it is only clearing the data in column B.

If someone could take a look at this I would be very greatful.

'Sub archive()
Dim i, lastrow
Dim mytext As String
lastrow = Sheets("Rooms").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lastrow
mytext = Sheets("Rooms").Cells(i, "F").Text
If InStr(mytext, "yes") Then
Sheets("Rooms").Cells(i, "A").EntireRow.Copy Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("Rooms").Cells(i, "B").Clear
End If
Next i
End Sub'

Answer 1

I've taken the cell on the associated row in column B and extended it to the last cell on the same row containing any value.

Sub archive()
    Dim i, lastrow
    Dim mytext As String

    With WorkSheets("Rooms")
        lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To lastrow
            mytext = .Cells(i, "F").Text
            If InStr(1, mytext, "yes", vbTextCompare) Then
                .Cells(i, "A").EntireRow.Copy Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
                .Range(.Cells(i, "B"), .Cells(i, Columns.Count).End(xlToLeft)).Clear
            End If
        Next i
    End With
End Sub

Additionally, I've used a With ... End With statement to associate WorkSheets("Rooms") with all of its cells to avoid repetitive worksheet referencing.

The Range.Clear command scrubs all values and formatting. If you just want the values to be removed, you may want to switch to Range.ClearContents method.