Fastest way to clear data

[JackDanIce]

Hi,

Curious about fastest way to clear data from a range, I created following and resulting output:

Sub testrun()


Dim x As Long


    For x = 1 To 3
        testtime x
    Next x
    Debug.Print ""
    
End Sub


Sub testtime(ByRef testvar As Long)


    Dim st  As Double
    Dim rng As Range
    
    st = Timer
    
    With Sheets("Sheet3")
        .Cells(1, 1).Resize(Rows.Count * 0.0005, Columns.Count * 0.25).Value = "xx"
        Set rng = .Cells(2, 1).Resize(LastRow(Sheets("Sheet3")) - 1, LastCol(Sheets("Sheet3")))
    End With
    
    Select Case testvar
        Case Is = 1: rng.ClearContents
        Case Is = 2: rng.Value = vbNullString
        Case Is = 3: rng.Value = Evaluate("IF(LEN(" & rng.Address & "), """")")
    End Select
    
    Set rng = Nothing
    
    Debug.Print "Testvar: " & testvar & " " & Round(Timer - st, 4) & " seconds"
    
End Sub

Testvar: 1 0.6289 seconds
Testvar: 2 0.5078 seconds
Testvar: 3 2.6914 seconds


Testvar: 1 0.5898 seconds
Testvar: 2 0.5117 seconds
Testvar: 3 2.707 seconds


Testvar: 1 0.6289 seconds
Testvar: 2 0.5078 seconds
Testvar: 3 2.7461 seconds

rng.value = vbNullString appears to be the fastest method but is there anything I haven't considered or overlooked? Any suggestions that are faster?

TIA,
Jack

 

[Jeffrey Mahoney]

I added a step and got the result below. I tried to find a VBA function like Union that would subtract a range from an area. Maybe like Subtract(). But no.

Sub testrun()




Dim x As Long




    For x = 1 To 4
        testtime x
    Next x
    Debug.Print ""
    
End Sub




Sub testtime(ByRef testvar As Long)




    Dim st  As Double
    Dim rng As Range
    
    st = Timer
    Set rng = Selection
    rng.Value = Range("G18:AO29").Value
    
'    With Sheets("Sheet3")
'        .Cells(1, 1).Resize(Rows.Count * 0.0005, Columns.Count * 0.25).Value = "xx"
'        Set rng = Selection.Cells(2, 1).Resize(LastRow(Sheets("Sheet3")) - 1, LastCol(Sheets("Sheet3")))
'    End With
    
    Select Case testvar
        Case Is = 1: rng.ClearContents
        Case Is = 2: rng.Value = vbNullString
        Case Is = 3: rng.Value = Evaluate("IF(LEN(" & rng.Address & "), """")")
        Case 4
          Dim Cel As Range
          Dim u As Range
          
          For Each Cel In rng
            If Cel.Value <> "" Then
              If Not u Is Nothing Then
                Set u = Union(u, Cel)
              Else
                Set u = Cel
              End If
            End If
          Next Cel
          If Not u Is Nothing Then u.Value = vbNullString
    End Select
    
    Set rng = Nothing
    
    Debug.Print "Testvar: " & testvar & " " & Round(Timer - st, 4) & " seconds"
    
End Sub

And got this as a result

Testvar: 1 0.1758 seconds
Testvar: 2 0.1484 seconds
Testvar: 3 0.1484 seconds
Testvar: 4 0.1562 seconds

 

[JackDanIce]

Hi Jeffrey Mahoney it seems like your PC runs faster

Though even with your 4th suggestion, vbNullString is still quicker and less complex coding/fewer lines to execute with #2 by your results

I just tried to find such a function and came across something like

Union(rng.Specialcells(xlcelltypeformulas), rng.specialcells(xlcelltypeconstants))[code]
but problem with that is if a formula evaluates to a blank, e.g. =IF(A1=1,"",A1)
 [MENTION=178213]Rick Rothstein[/MENTION] suggests a function for this in [url]https://www.mrexcel.com/forum/excel-questions/769643-selecting-non-blank-cells-vba.html[/url] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8]#8[/URL]  but again seems additional extra code over [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL]

 

[Fluff]

I got
Testvar: 1 1.1016 seconds
Testvar: 2 1.0469 seconds
Testvar: 3 1.0469 seconds

Testvar: 1 1.1562 seconds
Testvar: 2 1.0469 seconds
Testvar: 3 1.0312 seconds

Testvar: 1 1.1016 seconds
Testvar: 2 1.0469 seconds
Testvar: 3 1.0312 seconds


using

Sub testtime(ByRef testvar As Long)


    Dim st  As Double
    Dim rng As Range
    
    st = Timer
    
    With Sheets("Sheet3")
        .Cells(1, 1).Resize(Rows.count * 0.0005, Columns.count * 0.25).Value = "xx"
        Set rng = .UsedRange '.Cells(2, 1).Resize(LastRow(Sheets("Sheet3")) - 1, lastCol(Sheets("Sheet3")))
    End With
    
    Select Case testvar
        Case Is = 1: rng.ClearContents
        Case Is = 2: rng.Value = vbNullString
        Case Is = 3: rng.Value = ""
    End Select
    
    Set rng = Nothing
    
    Debug.Print "Testvar: " & testvar & " " & Round(Timer - st, 4) & " seconds"
    
End Sub

 

[JackDanIce]

Thanks fluff, would a reason for difference in yours be be ="" is shorter in char length than =vbNullString for the code to compline so faster to execute?

 

[Fluff]

I've no idea why it would be quicker, but 15 milliseconds is pretty irrelevant.

 

[JackDanIce]

It is, just curious - guess more when building larger models and making small gains all adds up, thanks for suggestion though

 

[Fluff]

You're welcome

 

[Jeffrey Mahoney]

vbNullString is a VBA constant that has to be retrieved. I would imagine that over millions of iterations one would see more of a difference.

I saw what Rothstein was trying to do. Looks like MS needs to add a SUBTRACT command!

 

[JackDanIce]

That's Jeff, makes sense vbNullString vs ""

Always things that can improve.. maybe in a future Excel/VBA release!