Replace method problems

[Guest]

Hi folks

I have a sub that should delete 3 from the strings in a selected range of
cells. It works for a single cell ( only if I if I include the
ActiveCell.Select line)

I get a message "Replace method of Range failed" with more than 1 cell
selected.

Please could anyone explain why it does not work with more than 1 selected
cell, and how I could get it to workfor a selection of cells?


Sub delete3()
Worksheets("Sheet1").Activate
'ActiveCell.Select
Selection.Replace What:="3", Replacement:="", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Thanks

 

[Gary Keramidas]

just modify the range to your needs

Sub delete3()
With Worksheets("Sheet1").Range("A1:F20")
..Replace What:="3", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

 

[Guest]

Thanks Gary

The problem is that the spreadsheet user is selecting cells on the
spreadsheet and it is this selection I need to use the replace method on.

Liz

 

[Gary Keramidas]

give this a try, just watch for word wrap


Sub delete3()
ActiveWorkbook.Names.Add Name:="test", RefersTo:="=" & ActiveSheet.Name & "!" &
_
ActiveWindow.RangeSelection.Address
With ActiveSheet.Range("test")
..Replace What:="3", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
End With
Range("test").Name.Delete
End Sub