CODEWITHSUNDEEP
excelvba
PhilNBlanks
PhilNBlanks

Reputation: 125

Is there a quick way to clear a large amount of data from a sheet?

I have the need to clear all data (except row 1) from two sheets in the same workbook. Currently one sheet has 21,000 rows and the other has 10,000 rows, both have 26 columns.

When I use the following code it takes approximately 2.5 minutes to execute.

There must be a faster way to do this as I can do it manually in less than 8 seconds.

Sub DeleteExceptFirst()
TurnOffFunctionality           'Turn off at the start
    Dim StartTime As Double
        StartTime = Timer         'Get start time
        
    Dim shCount As Integer
    Dim wks As Worksheet
    
    For shCount = 1 To 2
        If shCount = 1 Then
            Set wks = cnCustomers
        Else
            Set wks = cnVendors
        End If
    
    wks.Rows("2:" & Rows.Count).ClearContents
    Next shCount

TurnOnFunctionality            'Turn back on at the end
Call EndTimer("DeleteExceptFirst", StartTime)
End Sub

Upvotes: 0

Views: 338

Answers (2)

T.M.
T.M.

Reputation: 9948

You are deleting over the whole range size including all columns, but restricting deletion to contents only. As anything right of column Z may contain a whole bunch of blanks, extra formatting etc., VBA has a lot to do separating cell informations.

A simple way to provide for faster code execution might be to mark the entire columns block from column AA to XFD manually and to delete them completely. In a second step you can limit the pure content deletion to the 26 columns needed, e.g. via

            Dim header: header = wks.Range("A1:Z1")
            wks.Range("A:Z").ClearContents
            wks.Range("A1:Z1") = header

or alternatively to execute a complete clearing - not restricted to contents only - plus eventual (number/date) reformatting of the whole columns A:Z.

Of course a damaged workbook as mentioned by @JohnSUN in comment may cause most issues, so follow his advice then :-)

Upvotes: 1

Hooded 0ne
Hooded 0ne

Reputation: 997

I incorporated AcsErno's line into a simple loop for you.

Sub deleteallbutfirstrow()

Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning

For Each ws In ActiveWorkbook.Worksheets
    ws.UsedRange.Offset(1).ClearContents

Next

starting_ws.Activate 'activate the worksheet that was originally active

End Sub

Upvotes: 1

Related Questions