PDA

View Full Version : Solved: Do a .Replace on an inactive sheet



JimS
04-28-2010, 11:49 AM
I have a routine that runs perfect as long as the sheet is selected.

I'm trying to convert this code to run so that the sheets does not have to be selected (actually I would like the sheet to be hidden).

Can this routine be changed to do this?

Thanks for any help...

JimS



i = .Cells(.Rows.Count, "BA").End(xlUp).Row

Sheets("Data").Select
.Range("C2:C" & i).Select

For Each c In Selection
c.Value = re.Replace(c.Value, "")
Next c

For Each c In Selection
c.Value = rf.Replace(c.Value, "")
Next c

For Each c In Selection
c.Value = rg.Replace(c.Value, "")
Next c

For Each c In Selection
c.Value = rh.Replace(c.Value, "")
Next c

End With

JimS
04-28-2010, 05:08 PM
Figured it out. I needed the following:


With Worksheets("Data")

i = .Cells(.Rows.Count, "BA").End(xlUp).Row

End With

With Worksheets("Data").Range("C2:C" & i)

'not sure how you want to set the range to act on
' but this is quick and easy

'Sheets("Data").Select
'.Range("C2:C" & i).Select

For Each c In Selection
c.Value = re.Replace(c.Value, "")
Next c

For Each c In Selection
c.Value = rf.Replace(c.Value, "")
Next c

For Each c In Selection
c.Value = rg.Replace(c.Value, "")
Next c

For Each c In Selection
c.Value = rh.Replace(c.Value, "")
Next c

End With