• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

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
0
Kiwi-123
Asked:
Kiwi-123
  • 2
1 Solution
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now