The code in a standard module and a list of numbers in sheet 1, column A.
I run the code and it only works on whatever sheet is selected.

If the sheet is sheet 1 then it eliminates my list in column A.
If sheet 2 is selected and I run the code it works for that sheet on the scattered test numbers, and that sheet alone.

What is stopping the code from going to sheet 2, then 3 then 4 as I would think it should, given the array elements?

Thanks,
Howard

Option Explicit

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

Lrow = Range("A" & Rows.Count).End(xlUp).Row
Set DeleNum = Sheets("Sheet1").Range("A1:A" & Lrow)

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

For i = LBound(varSheets) To UBound(varSheets)
With Sheets(varSheets(i))
For Each c In DeleNum
Cells.Replace What:=c, Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next
End With
Next

End Sub

Hi Howard,

Am Fri, 22 Nov 2013 21:55:09 -0800 (PST) schrieb Howard:

The code in a standard module and a list of numbers in sheet 1, column A.
I run the code and it only works on whatever sheet is selected.

If the sheet is sheet 1 then it eliminates my list in column A.
If sheet 2 is selected and I run the code it works for that sheet on the scattered test numbers, and that sheet alone.

why do you delete cell by cell?

Try:

Sub XNumOut()

Dim DeleNum As Range
Dim c As Range
Dim rngC As Range
Dim varSheets As Variant
Dim i As Long
Dim LRow As Long

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

For i = LBound(varSheets) To UBound(varSheets)
With Sheets(varSheets(i))
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set DeleNum = .Range("A1:A" & LRow)
DeleNum.ClearContents
End With
Next

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Howard,

Am Sat, 23 Nov 2013 10:26:33 +0100 schrieb Claus Busch:

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

Hi Howard,

Am Sat, 23 Nov 2013 02:39:18 -0800 (PST) schrieb Howard:

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.

without the dot in front you haven't refered the range to look in to
your sheets

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.

If you want to avoid UsedRange you could set your correct range with
LRow and LCol, e.g.: .Range(Cells(1, 1), Cells(LRow, LCol))

I want to avoid cells because that means all cells of the sheet and
these are 17.179.869.184 in the newer excel versions


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

If you want to avoid UsedRange you could set your correct range with

LRow and LCol, e.g.: .Range(Cells(1, 1), Cells(LRow, LCol))



I want to avoid cells because that means all cells of the sheet and

these are 17.179.869.184 in the newer excel versions

Regards

Claus B.

Gotcha, UsedRange makes the most sense here as it might vary greatly between sheets.

And if the UsedRange is a million cells then it's taken care of as well as if it is only a few hundred.

Thanks for the further info.

Regards,
Howard