Hi Samantha,

Set rng = SH.UsedRange

instead of writing

Set rng = SH.Range("A1:D30")

the processing time becomes very very long. Why is that?

The used range is 9presumably) much larger than the previously specified
range.

If you are unable to restrict the range to specific columns . rows, perhaps
try:

'=============
Public Sub Tester2()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim CalcMode As Long
Dim ViewMode As Long

Set WB = Workbooks("YourBook3.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

On Error Resume Next
Set rng = SH.UsedRange. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo XIT

If Not rng Is Nothing Then
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveWindow
ViewMode = .View
.View = xlNormalView
End With

SH.DisplayPageBreaks = False

For Each rCell In rng.Cells
With rCell
rCell.Select
If Not IsEmpty(.Value) Then
If Not UCase(.Value) Like "*[A-Z]*" Then
.Replace What:=" ", Replacement:=""
End If
End If
End With
Next rCell
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

ActiveWindow.View = ViewMode

End Sub
'<<=============


---
Regards,
Norman