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]

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.

That is a good point which I had not considered.

 

[MARK858]

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

jiuk

Puzzled the one liner (with the Sub / End Sub) is a complete procedure/code.

 

[Twollaston]

Also If the OP had any formulae on the sheet, your code would have converted them to values.

Thank you for pointing that out. Luckily for me, my sheet gets bigger so I have to reapply the formulas anyway.


Can I ask you guys, if I run the arrays macro can I run it again on the same sheet after it gets repopulated with more data?
I just started learning about arrays and I know they get erased a lot at the end of the macro, so I just wanted to check.

Also I was wondering what does this part do? I looked up a tutorial on how a macro like this is working through the data, but I didn't find anything about the resizing
Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr


and thanks again everyone for your input, I certainly appreciate all the ideas.

 

[Fluff]

You can run the macro as often as you want, because it loads the data into the array at the start of the code.
The resize changes the size of an a range, in that case turns A1 into a range the same size as the original data
http://www.informit.com/articles/article.aspx?p=174338&seqNum=7

 

[Twollaston]

You can run the macro as often as you want, because it loads the data into the array at the start of the code.

Thank you for clarifying

The resize changes the size of an a range, in that case turns A1 into a range the same size as the original data
http://www.informit.com/articles/article.aspx?p=174338&seqNum=7

Sorry to bombard you with questions, Is this so I can call to the array later if I want to add something else at the end of the macro? I did read the article, but just trying to understand why we are resizing here. Trying to learn how to write these myself, for other future macros.

 

[Fluff]

No that just writes the array back to the sheet.

 

[Jack in the UK]

Puzzled the one liner (with the Sub / End Sub) is a complete procedure/code.

Hi Mark, its just a different approach. I am no fan of very short codes for most my the aim is to give readers starting blocks, something to play with,.edit, change etc the given code to assist learning. I am no expert and been on MrE from the start. I gave up over criticism to many and went to be Admin of OzGrid and I support Dave to the end. I drop my here and will if I fancy post a code or two to help if I can

In code there is no right or wrong way really, depends on experience and point of view and traditional ways as is common with most things

I write in a style that is not normal to most, I was kinda helped by MS MVPs when I used to meet them in London, many on here will remember me well enough as met loads of them. The reason is I enjoy a bit of excel but I am no way as smart as many others. A couple of the Microsoft boys when I went to Microsoft Reading conferences gave me this idea, which became my style and has stuck.

The codes will work and I test before posting, there should be no problem, its just about trying to help and give a little back

Happy posting and thank you for all you do and have done

Regards
jiuk

 

[MARK858]

I have no problems with different styles or suggestions (the more the merrier in fact), it was just the way it was written implied that the one liner wasn't a complete procedure.

Keep posting the suggestions always happy to read them

 

[Jack in the UK]

I have no problems with different styles or suggestions (the more the merrier in fact), it was just the way it was written implied that the one liner wasn't a complete procedure.

Keep posting the suggestions always happy to read them

Hey Mark, thanks for the reply

Just read read that, and was ment to be pointed at myself generally, no one else. What you say is correct, well 'unless' like me you execute in the immediate window of cause, always god for testing snippets

Regards
jiuk

 

[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

Hey everyone Sorry to bother you guys again,

I tried this macro today with real data from my report and I got a "Run-time error '13': Type mismatch" error

This is the debug line:
arr(x, i) = Replace(arr(x, i), "#", " ")

I was thinking this was due to my headers, throwing up the types so I changed:
arr = ActiveSheet.UsedRange

to:
arr = Range("A2", Range("A2").End(xlDown).End(xlToRight))

i was hoping that would bypass the error, but i'm still getting the error.
Is this because I don't have a type on the array?

My updated code is:

Sub NumToBlank()


    Dim arr
    Dim i As Long, x As Long
    
    Sheets("Dealer Locator and HAI View").Select
    
    arr = Range("A2", Range("A2").End(xlDown).End(xlToRight))
    
    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), "#", " ")
            arr(x, i) = Replace(arr(x, i), "000", " ")
        Next
    Next
    Range("A2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    
    Range("AB2:AB" & x).FormulaR1C1 = "=IF(RC[-9]=""1/1/0001"","" "",DATEVALUE(RC[-9]))"
    Range("AC2:AC" & x).FormulaR1C1 = "=IF(RC[-8]="" "", "" "",DATEVALUE(RC[-8]))"
    Range("AD2:AD" & x).FormulaR1C1 = "=IF(RC[-7]="" "", "" "",DATEVALUE(RC[-7]))"
    Range("AE2:AE" & x).FormulaR1C1 = "=MID(RC[-21],1,5)"

End Sub

Partial 1 row of data looks like(i excluded some columns because there is too many columns to show in this format and i removed sensitive data from cells) = All cells have ' at the start e.g. cell A2 shows 0003 but inside the cell '0003
All formatting in the home tab shows as General Format Type
(Data coming from BW)

NOTE: The # symbol can appear in any row, when data is absent, it is not constricted to the rows in the sample

0003

Southeast

3001

ADistrict

310

JC

jcemail

ABC

13245W

CANCELLED 09/18/97

USA

NC

#

#

#

#

9/6/1990

ABC, NC

#

#

9/18/1997

CANCELED

#

#

<tbody>
</tbody>

Just wondering if anyone had any ideas what I'm doing wrong here?

Thanks