Search and replace blanks
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
|