referring to workbook

[Guest]

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("A1100")
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

 

[Guest]

i forgot to add this to my other post, but since the workbook and sheet was
not specified, excel will assume it's the current workbook and current sheet,
meaning when you run the macro, it's only going to affect the selected sheet.

also, change the line
dim rng, rcell as range to
dm rng as range, rcell as range