Find and Replace Macro

[SammieH]

Hi all,

I'm very new to macros so I feel like this is pretty simple, but I just cant get there yet.

I have a very large file downloaded to Excel from SPSS. SPSS left behind a lot of #NULL ! in my spreadsheet.
Doing a simple CTRL+F and Replace will crash Excel because the file is so large.

I want to do this in a macro. I want it to search the entire spreadsheet and replace #NULL ! with "" (a blank cell).

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'; color: #000000 ; color: rgba(0, 0, 0, 0.85)}</style>I tried and got the following error:

Invalid outside procedure

This is the code I tried:

Sub RemoveNull()
'
' RemoveNull Macro
' Remove #NULL ! from SPSS Export
'


'
Cells.Select
Selection.Replace What:="#NULL !", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Thanks!

 

[tyija1995]

Hi Sammie,

Try this procedure (it will take some time to execute for a large spreadsheet however)

Sub ReplaceNulls()
    Dim rng As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
    Set rng = Selection
    For Each cell In rng
        If cell.Value = "#NULL !" Then
            cell.Value = ""
        End If
    Next cell
End Sub

 

[CsJHUN]

Hi SammieH,

this will erase every error from the sheet:

Sub SammieH_ErrorCleanUp()
Cells.Select
For Each cell In Selection
If IsError(cell.Value) Then cell.Value = ""
Next cell
End Sub

Good luck

 

[Fluff]

How about

Sub SammieH()
   Cells.SpecialCells(xlConstants, xlErrors).Value = ""
End Sub

 

[SammieH]

Thank you all! Problem Solved!

 

[Fluff]

Glad we could help & thanks for the feedback