why do you delete cell by cell?



I ran you code and so it deleted all cells I misunderstood your problem.



Try:



Sub XNumOut()



Dim DeleNum As Range

Dim c As Range

Dim rngC As Range

Dim varSheets As Variant

Dim DelC As Range

Dim i As Long

Dim Lrow As Long



With Sheets("Sheet1")

Lrow = .Cells(.Rows.Count, 1).End(xlUp).Row

Set DeleNum = .Range("A1:A" & Lrow)

End With



varSheets = Array("Sheet2", "Sheet3", "Sheet4")



For i = LBound(varSheets) To UBound(varSheets)

With Sheets(varSheets(i))

For Each c In DeleNum

.UsedRange.Replace What:=c, Replacement:="", _

LookAt:=xlPart, SearchOrder:=xlByRows, _

MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Next

End With

Next



End Sub





Regards

Claus B.



Thanks, Claus. That works fine indeed.

After comparing your solution to what I posted, I noticed the .UsedRange
(with the .)

So I re-tried my code with .Cells and it worked.

I guess the reason I used Cells is because I read some pro advice about UsedRange that gave me pause. Did not fully understand the reason to avoid it, (and there was no firm suggestion to ALWAYS avoid it) and may have been in a completely different situation.

He cited an experiment, which I cannot recall exactly, and I followed it and indeed it demonstrated how some confusion could occur.

I thought about using UsedRange here and decided not for those reasons.

Thanks for the help, and I will still keep UsedRange in my mind. I see it working on so many examples and I assume would be faster than .Cells.

Regards,
Howard