PDA

View Full Version : Delete duplicated rows 2



CicoMico
07-24-2007, 10:36 PM
hey! i thought that my problem with deleting duplicated rows is solved, but... i am getting mismatch error on this line:
If Application.CountIf(.Columns(TEST_COL), .Cells(i, TEST_COLUMN).Value) > 1 Then

if i do debug.print CountIf(.Columns(TEST_COL), it seems that this is the error part...

any help? thanx

xld's code:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Const TEST_COL As Long = 1 '<=== and this to match
Dim i As Long
Dim iLastRow As Long

With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If Application.CountIf(.Columns(TEST_COL), .Cells(i, TEST_COLUMN).Value) > 1 Then
.Rows(i).Delete
End If
Next i
End With
End Sub


Edited 25-Jul-07 by geekgirlau. Reason: insert vba tags

geekgirlau
07-24-2007, 11:21 PM
No error here ...

CicoMico
07-25-2007, 03:06 AM
No error here ... i know that there is no error in xld's code (he is the expert, i am just newbie), but i am trying to figure out, what can cause in my sheet this error to appear.

CicoMico
07-25-2007, 07:13 AM
maybe I have found one problem... if i have empty cell, it doesn't do the job. any help?

xld
07-25-2007, 07:17 AM
CicoMico,

I have tested it with various combinations, and don't get the error (including empty cells).

Can you post an example workbook?

CicoMico
07-25-2007, 12:21 PM
CicoMico,

I have tested it with various combinations, and don't get the error (including empty cells).

Can you post an example workbook?
here is the example workbook... i think the error is generated at C1 cell - Run-time error '13': Type mismatch

xld
07-25-2007, 01:21 PM
No attachment mate!

CicoMico
07-25-2007, 02:18 PM
Ups, sorry! here it goes!

geekgirlau
07-26-2007, 07:22 PM
Const TEST_COLUMN As String = "C" '<=== change to suit
Const TEST_COL As Long = 3 '<=== and this to match
Dim it As Long
Dim iLastRow As Long

With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For it = iLastRow To 1 Step -1
If Application.CountIf(.Columns(TEST_COL), .Cells(it, TEST_COLUMN)) > 1 Then
.Rows(it).Delete
End If
Next it
End With