macro to delete text

[nicko]

hi all,

i need a macro to delete the word "NULL" when it appears in a cell in work sheet.

I used macro recorder to come up wit the following code. The problem is whenever i attempt to run this it takes over 4 hours and never completes.

 Range("I10:BH10").Select
 Range(Selection, Selection.End(xlDown)).Select
 Range("I10:BH25000").Select
 Selection.Replace What:="NULL", Replacement:=" ", LookAt:=xlPart, _
 SearchOrder:=xlByRows, MatchCase:=False

Does anyone have another solution I can try that maybe faster?

Many thanks,

Nicko

[shamsam1]

u can call this macro
here i am checking with colum A


Sub delete()
Dim calcmode As Long
Dim ViewMode As Long
Dim myStrings As Variant
Dim FoundCell As Range
Dim I As Long
Dim myRng As Range
Dim sh As Worksheet

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can also use Sheets("MySheet")
Set sh = ActiveSheet

'We search in column A in this example
Set myRng = sh.Range("A:A")

'Add more search strings if you need
myStrings = Array("null")


With sh

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'We will search the values in MyRng in this example
With myRng

For I = LBound(myStrings) To UBound(myStrings)
Do
Set FoundCell = myRng.Find(What:=myStrings(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


'Use xlPart If you want to search in a part of the FoundCell
'If you use LookIn:=xlValues it will also delete rows with a
'formula that evaluates to "null"
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.Cells.Clear
End If
Loop
Next I

End With

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With



End Sub

[mikerickson]

You could record a macro of using Find/Replace to find NULL and replace with nothing.

[nicko]

I originally tried a find/replace macro to find NULL and replace with nothing, but unfortunately was taking over 4 hours and crashing.

Shamsam1's macro is working, but also operating pretty slowly.

[shamsam1]

check for given range dont check whole column the it will work fast

[nicko]

just adjusted it so it works for the range not the whole column and it is working faster.

I need to check across 50 columns so that is why it takes so long.

If i run the macro for one column at a time it is very fast, but this means have to keep adjusting the macro for the next column.

Is there a way to adjust it so it works down one column then moves onto the next, until there is no columns left to check?