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!
help---11-09-2012.xls
Kiwi-123Asked:
Who is Participating?
 
nutschCommented:
Hi, try this code. I've shifted from a loop logic to an autofilter, which will perform much faster, especially on larger datasets.

Thomas

Private Sub CommandButton3_Click()
Application.ScreenUpdating = False
    
    With Sheets("Low Volume").[B8].CurrentRegion
        .AutoFilter
        .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
        .AutoFilter
        
    End With
    
Application.ScreenUpdating = True
       
End Sub

Open in new window

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

Many thanks for your help!
0
 
nutschCommented:
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.

Thomas
0
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.