Runtime Error 7 - Out of Memory when using RegExes

hrlarry

New Member
Joined
Jun 22, 2015
Messages
2
Hey there,

I'm trying to write a program that automatically finds and removes a set of strings (stored in one column) from a large dataset. Here's my code:

Code:
Sub Redact()    
    
    Dim strPattern As String
    Dim strReplace As String: strReplace = "[Redacted]"
    
    Dim regEx As New RegExp ' For detection
    Dim nameRegEx As New RegExp ' For extraction
    Dim strInput As String
    Dim NameRange As Range
    Dim ReplaceRange As Range
    
    Set ReplaceRange = Application.InputBox("Select the range that you want to redact names from", "Obtain Range Object", Type:=8)
    Set NameRange = Application.InputBox("Select the range that contains the names you want redacted", "Obtain Range Object", Type:=8)
    
    NameRange.Sort Key1:=NameRange.Worksheet.Range("A1"), Order1:=xlDescending


    Application.ScreenUpdating = False
    Application.EnableEvents = False


    With regEx
        .MultiLine = True
        .IgnoreCase = False
    End With


    With nameRegEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
    End With


    Dim currName As String 'Keeps track of the name currently being redacted


    For Each cell2 In NameRange
    
        currName = cell2.Value
        
        strPattern = "(" + currName + "[\s\.\?!:;,]([A-Z]\.? )?)|(" + currName + "$)|(" + currName + "'s)"


        For Each cell In ReplaceRange
            
            If strPattern <> "" Then
                strInput = cell.Value
    
                regEx.Pattern = strPattern
    
                If regEx.Test(strInput) Then
                    nameRegEx.Pattern = currName + "((\s[A-Z]\.?$)|(\s[A-Z]\.?[^a-z]))?[^a-z]|(" + currName + "((\s[A-Z]\.?$)|(\s[A-Z]\.?[^a-z]))?$)"
                    cell.Value = nameRegEx.Replace(strInput, strReplace)
                End If
                
            End If
        Next
    Next
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

I was able to run the code many times without fail (on about 300-500 lines/1800-3000 cells of data at once), but now I keep getting the out of memory issue. I'm thinking I need to flush some of the memory, but I would also love to know how I can make the code cleaner to avoid creating this problem again no matter how many iterations I run the code. I am getting the error on this line:

Code:
cell.Value = nameRegEx.Replace(strInput, strReplace)


I've added this near the end of the routine, but can't even reach it anymore. Would this help anyway?

Code:
    Set regEx = Nothing

    Set nameRegEx = Nothing

Thanks so much!
 

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.

hrlarry

New Member
Joined
Jun 22, 2015
Messages
2
Turns out, the code was fine, I was just dealing with strings that were too long and exceeded the character limit. This is now solved.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,269
Messages
5,635,194
Members
416,846
Latest member
ImGoing2needaFormula

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
Top