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