With Sheets("LoadList Processing").Select '(Sheet 3)

Just trouble editing. The .Select should be gone

With Sheets("LoadList Processing").


Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
Sheets("LoadList Processing").Select '(Sheet 3)



ActiveSheet.Range(Selection, "A1:A1000").Select



Dim Col As Integer

Dim r As Long

Dim C As Range

Dim N As Long

Dim V As Variant

Dim Rng As Range

dim rngSh3 as Range


On Error GoTo EndMacro

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual



Col = ActiveCell.Column



If Selection.Rows.Count 1 Then

Set Rng = Selection

Else

Set Rng = ActiveSheet.UsedRange.Rows

End If



N = 0

For r = Rng.Rows.Count To 1 Step -1

V = Rng.Cells(r, 1).Value

If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then

Rng.Rows(r).EntireRow.Delete

N = N + 1

End If

Next r



EndMacro:

Application.Calculation = xlCalculationAutomatic



'Comment: the code above works fine. The code below fails.



With Sheets("LoadList Processing").Select '(Sheet 3)

set rngSH3 = .Cells(1,2)


findstring = "Digibeta"

Set b = .Columns(rngSh3.column).Find(What:=findstring, _
LookAt:=xlWhole)

While Not (b Is Nothing)

b.EntireRow.Delete

Set b = .Columns(rngSh3.Column).Find(What:=findstring, _
LookAt:=xlWhole)

Wend



set rngSh3:=.Cells(1,3)



findstring = "Record"

Set b = .Columns(rngSh3.column).Find(What:=findstring, _
LookAt:=xlWhole)

While Not (b Is Nothing)

b.EntireRow.Delete

Set b = .Columns(rngSh3.column).Find(What:=findstring, _
LookAt:=xlWhole)

Wend

Application.CutCopyMode = False

End With

Unqualifed references to ranges in a sheet module refer to the sheet of
the
sheet module, not the activesheet.

Regards,
Tom Ogilvy


PJF wrote in message
...


A workbook contains three worksheets: (1) "Server Delete List", (2)
"Data
Processing Sheet" and (3) "Loadlist Processing". The code is run from a
control button on "Server Delete List" worksheet (Sheet 1). That code
runs
fine in processing data on the "Data Processing Sheet" (Sheet 2).

On Sheet 3, the code deletes entire rows based on several criteria:
duplicate numbers, cells containing the strings "Digibeta" and "Record".
It
runs fine in processing the duplicate number row delete function code on
the
"Loadlist Processing" worksheet (Sheet 3). But, it fails when trying to
run
the "Digibeta" and "Record" row delete code on Sheet 3.

I should note that both the "Digibeta" and "Record" row delete code runs
fine when run from a macro created on and run from Sheet 3. So, I
suspect
that I have omitted one or more references to Sheet 3 ("Loadlist
Processing") in the code that runs from Sheet 1 for these two
operations.
But, I can't figure out what!

Any suggestions would be most welcomed.

Thanks and regards,

PJF
__________________________________________________ ____________________
Here is the relevant code:

Sheets("LoadList Processing").Select '(Sheet 3)



ActiveSheet.Range(Selection, "A1:A1000").Select



Dim Col As Integer

Dim r As Long

Dim C As Range

Dim N As Long

Dim V As Variant

Dim Rng As Range



On Error GoTo EndMacro

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual



Col = ActiveCell.Column



If Selection.Rows.Count 1 Then

Set Rng = Selection

Else

Set Rng = ActiveSheet.UsedRange.Rows

End If



N = 0

For r = Rng.Rows.Count To 1 Step -1

V = Rng.Cells(r, 1).Value

If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then

Rng.Rows(r).EntireRow.Delete

N = N + 1

End If

Next r



EndMacro:

Application.Calculation = xlCalculationAutomatic



'Comment: the code above works fine. The code below fails.



Sheets("LoadList Processing").Select '(Sheet 3)

Application.Goto Reference:="R1C2"

ActiveSheet.Range(Selection, Selection.End(xlDown)).Select

findstring = "Digibeta"

Set b = Columns(ActiveCell.Column).Find(What:=findstring,
LookAt:=xlWhole)

While Not (b Is Nothing)

b.EntireRow.Delete

Set b = Columns(ActiveCell.Column).Find(What:=findstring,
LookAt:=xlWhole)

Wend

Application.CutCopyMode = False





Sheets("LoadList Processing").Select '(Sheet 3)

Application.Goto Reference:="R1C3"

ActiveSheet.Range(Selection, Selection.End(xlDown)).Select

findstring = "Record"

Set b = Columns(ActiveCell.Column).Find(What:=findstring,
LookAt:=xlWhole)

While Not (b Is Nothing)

b.EntireRow.Delete

Set b = Columns(ActiveCell.Column).Find(What:=findstring,
LookAt:=xlWhole)

Wend

Application.CutCopyMode = False



'Comment: As noted in the message, all of this code works fine when
run
from a macro created on and run from Sheet 3.