Looking for a faster way to find and replace

Twollaston

Board Regular
Joined
May 24, 2019
Messages
241
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.


Code:
Sub NtoBlank()


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

End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
This should be considerably faster...

Code:
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
 
Upvote 0
This should be considerably faster...

Code:
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
 
Upvote 0
You're welcome, I was happy to help. Thanks for the feedback!
 
Upvote 0
You could also limit the replace to the used range, rather than searching every single cell on the sheet
Code:
Sub NtoBlank()
   ActiveSheet.UsedRange.Replace What:="#", Replacement:=""
End Sub
 
Upvote 0
Code:
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
 
Upvote 0
@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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top