Looking for a faster way to find and replace

[Twollaston]

Hello there,

I have a worksheet with about 120,000 lines with probably 50 columns and I run the following code to change all the #'s to blank cells. I was wondering if there is a faster way to do this, because this takes a long time to complete.

Sub NtoBlank()


             Cells.Replace What:="#", Replacement:=""

End Sub

 

[igold]

This should be considerably faster...

Sub test()


    Dim arr
    Dim i As Long, x As Long
    
    arr = ActiveSheet.UsedRange
    For x = LBound(arr, 1) To UBound(arr, 1)
        For i = LBound(arr, 2) To UBound(arr, 2)
            arr(x, i) = Replace(arr(x, i), "#", "")
        Next
    Next
    Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    MsgBox "Operation Complete"
    
End Sub

 

[Twollaston]

This should be considerably faster...

Sub test()


    Dim arr
    Dim i As Long, x As Long
    
    arr = ActiveSheet.UsedRange
    For x = LBound(arr, 1) To UBound(arr, 1)
        For i = LBound(arr, 2) To UBound(arr, 2)
            arr(x, i) = Replace(arr(x, i), "#", "")
        Next
    Next
    Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    MsgBox "Operation Complete"
    
End Sub

Wow! That is much faster
My excel was on the brink of breaking down with the old code

Thank you very much, well done

 

[igold]

You're welcome, I was happy to help. Thanks for the feedback!

 

[Fluff]

You could also limit the replace to the used range, rather than searching every single cell on the sheet

Sub NtoBlank()
   ActiveSheet.UsedRange.Replace What:="#", Replacement:=""
End Sub

 

[Jack in the UK]

Sub NtoBlank()
'// jiuk - Removes "ALL" # characters in active sheet

Const myFIND As String = "#"
Const myREPLACE As String = ""

Dim Target_Sheet As Worksheet
Dim Target_RANGE As Excel.Range

Set Target_Sheet = ActiveSheet
Set Target_RANGE = Target_Sheet.UsedRange

Target_RANGE _
.Replace _
    What:=myFIND, _
    Replacement:=myREPLACE

theEND:
Set Target_WorkBook = Nothing
Set Target_Sheet = Nothing
Set Target_RANGE = Nothing

Exit Sub
End Sub

 

[igold]

@Fluff - I had considered that option and after testing on a small dataset, decided it was not going to be as fast. Now that you have suggested the same thing, just to be sure I ran both codes on 120k rows and 50 columns of data.

On my machine my code took about 7.50 seconds to complete. Using your code, I manually stopped the code after 5 minutes of running and when I scrolled through the data at that point, it had only completed about 60k rows of data.

Just a friendly fyi.

Regards,

igold

 

[Fluff]

I was not saying it would be any quicker than your code, I was just pointing out that it's better to limit it to the used range.
Also If the OP had any formulae on the sheet, your code would have converted them to values.

 

[Fluff]

@Jack in the UK
Isn't that a rather long winded way of writing the same thing as Post#5

 

[Jack in the UK]

@Jack in the UK
Isn't that a rather long winded way of writing the same thing as Post#5

Offering a different approach that's all - giving a complete procedure/code rather that one liners

jiuk