Macro Help, Please
Tom,
Thanks so much for your quick reply.
Below is the code that I incorporated from you message. However, I do get a "Run-Time Error '424', Object Required" when stepping through the code that starts:
With Sheets("LoadList Processing").Select '(Sheet 3)
set rngSH3 = .Cells(1,2)
Interestingly, when I run the code from the top up to the first instance of line that begins:
With Sheets("LoadList Processing").Select '(Sheet 3)
and as I step through the code (press F8), it first steps through the two lines of code above, then jumps up to the line with the code:
Application.Calculation = xlCalculationAutomatic
Then, steps through the same two lines of code above, then I get the Error 424 message.
See anything I overlooked in implementing your suggested code?
Thanks and regards,
PJF
__________________________________________________ _____________________
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
'Application.ScreenUpdating = False
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
With Sheets("LoadList Processing").Select '(Sheet 3)
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
"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.
|