Looking for a faster way to find and replace

Twollaston

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

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Twollaston

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

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You're welcome, I was happy to help. Thanks for the feedback!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,290
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

@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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,290
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,290
Office Version
  1. 365
Platform
  1. Windows
@Jack in the UK
Isn't that a rather long winded way of writing the same thing as Post#5 ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,951
Messages
5,525,846
Members
409,667
Latest member
jwieting

This Week's Hot Topics

Top