Help with Macro in Excel 2003

[Coleman1]

I have been using the following code in Excel 2007 and it has been working
fine. I need for the code to work just as well in Excel 2003 but it runs
slower and I get a Runtime Error 438:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wCtr As Long
Dim w As Worksheet
Dim myNames As Variant
Sheets(Array("Carrier Rates", "Template")).Select
Sheets("Carrier Rates").Activate
Rows("2:2").Select
Selection.AutoFill Destination:=Rows("2:3000"), Type:=xlFillDefault
Rows("2:3000").Select
Sheets("TEMPLATE").Activate
Rows("2:2").Select
Selection.AutoFill Destination:=Rows("2:3000"), Type:=xlFillDefault
Rows("2:3000").Select
Sheets("Template").Select
ActiveSheet.Range("$A$1:$AC$3000").RemoveDuplicates Columns:=Array(1, 2,
3, 4, 5 _
, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22,
23, 24, 25, 26, 27, 28, 29), Header _
:=xlYes
Sheets("Carrier Rates").Select
ActiveSheet.Range("$A$2:$Y$3000").RemoveDuplicates Columns:=Array(1, 2,
3, 4, 5, 6 _
, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23,
24, 25), Header:=xlYes
End Sub


I am getting a Runtime error on "RemoveDuplicates" because it is not
recognized in Excel 2003. Any suggestions to increase speed and eliminate
the Runtime Error?

 

[Ronald R. Dodge, Jr.]

I am not sure which workbook this is in, but try the following under the
assumption that the code is in the same workbook as the sheets you are
attempting to adjust.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wCtr As Long
Dim w As Worksheet
Dim myNames As Variant
ThisWorkbook.Worksheets("Carrier Rates").Range("2:3000").FillDown
ThisWorkbook.Worksheets("TEMPLATE").Range("2:3000").FillDown
ThisWorkbook.Worksheets("TEMPLATE").Range("$A$1:$AC$3000"). _
RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, _
6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23,
24, 25, 26, 27, 28, 29), _
Header :=xlYes
ThisWorkbook.Worksheets("Carrier Rates").Range("$A$2:$Y$3000"). _
RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24,
25), Header:=xlYes
End Sub

Note, anything that deals with either Select or Activate, I got rid of them
as they are more of a headache to deal with than what it's worth. Those 2
methods should only be used if there is no other way to get around such
issues which I do have one such case dealing with using a third party
program and I have sent them the suggestions to fix their code, but they
have yet to do it.

If the worksheets is not in the same workbook as this code, then replace:

ThisWorkbook

with:

Workbook("Book1.xls")

where "Book1.xls" is the file spec name of the Excel file that contains the
worksheets you are attempting to modify.


Also, Excel 2003 does not have a "RemoveDuplicates" method on the Range
Object (Or according to documentation, known as Range Property), so you
would need to use the one of the recursive means rather it be:

For Each cell in Range
....
Next

or:

For I = # To # Step #
....
Next I

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Coleman1]

Ron thanks it does run alot faster in Excel 2007. I am still getting the
error the RemoveDuplicates is not supported in Excel 2003. Any suggestions?

 

[Ronald R. Dodge, Jr.]

Excel 2003 does not have the method of "RemoveDuplicates", so to check for
duplicates, you would have to manually write a function (Note, in this case,
it's a VBA function strictly to use within VBA, not a User Defined Function
(UDF) that is used within formulas on the spreadsheet side. Though the
creation of a such function is the same as the UDF, some of the restrictions
that's in place for UDFs, it's not there for regular VBA functions.) that
would check for the duplicates.

A VBA function can be used to perform action and then return its result(s)
to the method that called on. Some developers use "Function" more heavily
than "Sub" just for this very reason.

In your case, this would require recursive coding that check through the
various cells. The one big warning with recursive coding, be careful as
recursive coding can get caught in an infinite loop or take up excessive
amount of time to process. If you ever used SUMIF or COUNTIF functions
within Excel and noticed how much longer it takes to have the formulas
calculate, exponentially speaking, as the number of rows increase within the
source table as well as within the destination table, recursive coding has
the same result. SumIf and recursive coding can take up excessive amount of
memory as well, if not careful with their usage.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000