Getting macro to run with pasted function
 

Greetings,

I am trying to remove all duplicate rows by checking the first five colums
in each row. The spreadsheet is 20 colums by 800 rows, and I am using Excel
2003.

I have tried to get Chip Pearson's DeleteDuplicatesViaFilter to run, and I'm
not having any luck. Don't have much, (if any) experience with Visual Basic.
I'm not sure where to put the range of cells I want checked. I also don't
know how to name the macro to be able to make it run. Is this the best
solution? Any help would be greatly appreciated.

Here is the first part of his code:

Function DeleteDuplicatesViaFilter(ColumnRangeOfDuplicates As Range) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
' DeleteDuplicatesViaFilter
' This function uses Advanced Filter to remove duplicate records from
' the rows spanned by ColumnRangeOfDuplicates. A row is considered to
' be a duplicate of another row if the columns spanned by
ColumnRangeOfDuplictes
' are equal. Columns outside of those spanned by ColumnRangeOfDuplicates
' are not tested. The function returns the number of rows deleted, including
' 0 if there were no duplicates, or -1 if an error occurred, such as a
' protected sheet or a ColumnRangeOfDuplicates range with multiple areas.
' Note that Advanced Filter considers the first row to be the header row
' of the data, so it will never be deleted.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''

Dim DeleteRange As Range
Dim Rng As Range
Dim SaveCalc As Long
Dim SaveEvents As Long
Dim SaveUpdating As Long
Dim BeginRowCount As Long
Dim EndRowCount As Long

--
Thanks, Glenn

Getting macro to run with pasted function
 

Glenn,

You need to call the function from a subroutine. The example below works to
eliminate duplicates based on values in the first 5 columns, and assumes
that you have labels in row 1, and no gaps in your data set:

Sub TestForGlenn()
MsgBox "The function deleted " & _
DeleteDuplicatesViaFilter( _
Intersect(Range("A:E"), Range("A1").CurrentRegion)) & _
" records."
End Sub

Function DeleteDuplicatesViaFilter(ColumnRangeOfDuplicates As Range) As Long
'rest of function code here....


Simply run the macro 'TestForGlenn'

HTH,
Bernie
MS Excel MVP


"Glenn" wrote in message
...
Greetings,

I am trying to remove all duplicate rows by checking the first five colums
in each row. The spreadsheet is 20 colums by 800 rows, and I am using
Excel
2003.

I have tried to get Chip Pearson's DeleteDuplicatesViaFilter to run, and
I'm
not having any luck. Don't have much, (if any) experience with Visual
Basic.
I'm not sure where to put the range of cells I want checked. I also don't
know how to name the macro to be able to make it run. Is this the best
solution? Any help would be greatly appreciated.

Here is the first part of his code:

Function DeleteDuplicatesViaFilter(ColumnRangeOfDuplicates As Range) As
Long
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
' DeleteDuplicatesViaFilter
' This function uses Advanced Filter to remove duplicate records from
' the rows spanned by ColumnRangeOfDuplicates. A row is considered to
' be a duplicate of another row if the columns spanned by
ColumnRangeOfDuplictes
' are equal. Columns outside of those spanned by ColumnRangeOfDuplicates
' are not tested. The function returns the number of rows deleted,
including
' 0 if there were no duplicates, or -1 if an error occurred, such as a
' protected sheet or a ColumnRangeOfDuplicates range with multiple areas.
' Note that Advanced Filter considers the first row to be the header row
' of the data, so it will never be deleted.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''

Dim DeleteRange As Range
Dim Rng As Range
Dim SaveCalc As Long
Dim SaveEvents As Long
Dim SaveUpdating As Long
Dim BeginRowCount As Long
Dim EndRowCount As Long

--
Thanks, Glenn

Getting macro to run with pasted function
 

Bernie,

Thank you so much, that did the trick.
--
Glenn


"Bernie Deitrick" wrote:

Glenn,

You need to call the function from a subroutine. The example below works to
eliminate duplicates based on values in the first 5 columns, and assumes
that you have labels in row 1, and no gaps in your data set:

Sub TestForGlenn()
MsgBox "The function deleted " & _
DeleteDuplicatesViaFilter( _
Intersect(Range("A:E"), Range("A1").CurrentRegion)) & _
" records."
End Sub

Function DeleteDuplicatesViaFilter(ColumnRangeOfDuplicates As Range) As Long
'rest of function code here....


Simply run the macro 'TestForGlenn'

HTH,
Bernie
MS Excel MVP


"Glenn" wrote in message
...
Greetings,

I am trying to remove all duplicate rows by checking the first five colums
in each row. The spreadsheet is 20 colums by 800 rows, and I am using
Excel
2003.

I have tried to get Chip Pearson's DeleteDuplicatesViaFilter to run, and
I'm
not having any luck. Don't have much, (if any) experience with Visual
Basic.
I'm not sure where to put the range of cells I want checked. I also don't
know how to name the macro to be able to make it run. Is this the best
solution? Any help would be greatly appreciated.

Here is the first part of his code:

Function DeleteDuplicatesViaFilter(ColumnRangeOfDuplicates As Range) As
Long
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
' DeleteDuplicatesViaFilter
' This function uses Advanced Filter to remove duplicate records from
' the rows spanned by ColumnRangeOfDuplicates. A row is considered to
' be a duplicate of another row if the columns spanned by
ColumnRangeOfDuplictes
' are equal. Columns outside of those spanned by ColumnRangeOfDuplicates
' are not tested. The function returns the number of rows deleted,
including
' 0 if there were no duplicates, or -1 if an error occurred, such as a
' protected sheet or a ColumnRangeOfDuplicates range with multiple areas.
' Note that Advanced Filter considers the first row to be the header row
' of the data, so it will never be deleted.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''

Dim DeleteRange As Range
Dim Rng As Range
Dim SaveCalc As Long
Dim SaveEvents As Long
Dim SaveUpdating As Long
Dim BeginRowCount As Long
Dim EndRowCount As Long

--
Thanks, Glenn