One of your dim statments is incorrect. Take a look at this site for an
explanation...

http://www.cpearson.com/excel/variables.htm

I have change the one line necessary to refer to the activeworkbook...

Public Sub findAndRemoveBlanks()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng as range, rCell As Range

Set WB = activeworkbook 'Change to active workbook

--
HTH...

Jim Thomlinson


"Fabrizio" wrote:

I have a sub that searches for blanks and removes them. however someone
helped me with the code and i do not know how to write it so that it works
for any workbook. In the code below a specific workbook is used but if I save
the file under different name it does not work. how do you write the code so
that it refers to the current workbook that is open?


Public Sub findAndRemoveBlanks()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng, rCell As Range

Set WB = Workbooks("20060619c")
Set SH = WB.Sheets("Indata")
Set rng = SH.UsedRange
'Set rng = SH.Range("A1:p100")
For Each rCell In rng.Cells
With rCell
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 Sub