Search/Replace in defined array VBA

[jdaywalt]

I am just starting to learn about using arrays in VBA to improve efficiency when performing various functions across large data sets. I have an array defined as follows:

Dim MyArray as Variant
MyArray = Range("BS27:DS50000")


The values in this range will either contain text or a value of zero. I want to replace all zeroes with a blank/null value. How do I write the code that does this? I have recorded the search & replace code but I believe the syntax would be different when using an array vs. actually selecting the range from the worksheet. Just FYI, here is the recorded code (minus apostrophes, of course):

' Range("BS27:DS50000").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
' SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
' ReplaceFormat:=False
' Range("BS27").Select

 

[p45cal]

maybe:

Dim MyArray As Variant
MyArray = Range("BS27:DS50000")
For i = LBound(MyArray) To UBound(MyArray)
    For j = LBound(MyArray, 2) To UBound(MyArray, 2)
        If MyArray(i, j) = 0 Then MyArray(i, j) = Empty
    Next j
Next i
'Range("A1:D3") = MyArray

 

[jdaywalt]

Great, so far so good! However, I forgot to ask proper syntax for "copying" back into the original range after the search/replace has been performed. Can you help with that too please?

 

[Rick Rothstein]

The values in this range will either contain text or a value of zero. I want to replace all zeroes with a blank/null value. How do I write the code that does this? I have recorded the search & replace code but I believe the syntax would be different when using an array vs. actually selecting the range from the worksheet. Just FYI, here is the recorded code (minus apostrophes, of course):

I would expect simply replacing the zeroes with nothing would be the fastest way to do this...

Sub ReplaceZeroesWithEmptyString()
  Range("BS27:DS50000").Replace 0, "", xlWhole
End Sub

 

[jdaywalt]

Yes, Rick---that indeed worked very well! I thought the array method would be faster, but actually yours worked just as quickly. Appreciate the alternative approach!

 

[p45cal]

Great, so far so good! However, I forgot to ask proper syntax for "copying" back into the original range after the search/replace has been performed. Can you help with that too please?

The answer is the penultimate line of blah below.
The array method is faster if there are a lots of zeroes. If there are none the replace method is faster.
Trials with all zeroes in the range was 14 times as fast on my machine. Application.ScreenUpdating = False made little difference.

The two subs used for testing:

Sub blah()
Dim MyArray As Variant
Range("BS27:DS5000") = 0
MyArray = Range("BS27:DS5000")
starttime = Timer
For i = LBound(MyArray) To UBound(MyArray)
    For j = LBound(MyArray, 2) To UBound(MyArray, 2)
        If MyArray(i, j) = 0 Then MyArray(i, j) = Empty
    Next j
Next i
[B][COLOR=#0000ff]Range("BS27:DS5000") = MyArray[/COLOR][/B]
MsgBox Timer - starttime
End Sub



Sub ReplaceZeroesWithEmptyString()
Range("BS27:DS5000") = 0
starttime = Timer
Range("BS27:DS5000").Replace 0, "", xlWhole
MsgBox Timer - starttime
End Sub

I took a nought off your original range as my machine was starting to bend under the weight.

 

[jdaywalt]

I will try both methods and see which works best (and I love your timer function---I'm stealing that too!) Either way, I love that I now have two methods in my arsenal for current AND future use. Thank you so very much!