Change delete row macro to delete cell range

Posted on 2012-09-20
Last Modified: 2012-09-20
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!
Question by:Kiwi-123
    LVL 39

    Accepted Solution

    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


    Author Comment

    Perfect, works better than I'd even imagined.

    Many thanks for your help!
    LVL 39

    Expert Comment

    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.


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now