Change delete row macro to delete cell range

Please can you help with the attached?

Basically the command button "Archive" moves the values occupied in column B:S onto the next worksheet "Archive"

The problem I have is that I don't want to delete the rows transferred, I would just like to clear the values in column B:N & R

The reason is that the formulas need to stay in situ and will reset once the values have been cleared.

If possible I would like to cap the limit on the "Archive" from line 9 to line 60

One other thing is that unless a date has been added in column N, the line will stay upon the current worksheet.

If you press the command button it should demostrate

Many thanks for your help!
Who is Participating?
Hi, try this code. I've shifted from a loop logic to an autofilter, which will perform much faster, especially on larger datasets.


Private Sub CommandButton3_Click()
Application.ScreenUpdating = False
    With Sheets("Low Volume").[B8].CurrentRegion
        .AutoFilter field:=13, Criteria1:="<>"
        With .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
            .Copy Sheets("Archive").Cells(Rows.Count, 2).End(xlUp).Offset(1)
            .Resize(, 13).ClearContents
            .Resize(, 1).Offset(, 16).ClearContents
        End With
    End With
Application.ScreenUpdating = True
End Sub

Open in new window

Kiwi-123Author Commented:
Perfect, works better than I'd even imagined.

Many thanks for your help!
Glad to help. Thanks for the grade.

One thing though, if you were to convert your range to a table, the formulas would come automatically when you add a line, which could save you from having to keep them in all the time.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.