VBA Excel Removing duplicate rows

4
votes

So I'm trying to remove any rows that have a duplicate in column C. This is a column of about 700 records however this value varies with different data used hence I've implemented a "LastRow" function. Here's my code:

Public Function LastRowInCRC() As Long

    Dim wsCRC As Worksheet
    Set wsCRC = Worksheets("CRC")

    With wsCRC
        LastRowInCRC = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With

End Function

Sub DeleteDupRowsCRC()

    Dim wsCRC As Worksheet
    Set wsCRC = Worksheets("CRC")

    Dim lrowcrc As Long
    lrowcrc = CRC.LastRowInCRC

    'Debug.Print "C8:C" & lrowcrc

    With wsCRC

        .Range("C8:C" & lrowcrc).RemoveDuplicates Columns:=Array(3)

    End With

End Sub

I get the "application-defined or object-defined" error at the following line when I step by step debug:

.Range("C8:C" & lrowcrc).RemoveDuplicates Columns:=Array(3)

Any ideas whats going wrong? I call the "C8:C" & lrowcrc to the immediate window which is commented out, and it gives me the correct range values so I don't think the issue is in that but I cannot find whats wrong... any help greatly appreciated.

2
vbaexcel

2 Answers

3
votes

In general, change Array(3) to Array(1) and it may work.

Not general: The following works for me, removing the duplicates in column C Make sure that you work on the first Worksheet:

Option Explicit

Public Function LastRowInCRC() As Long

    Dim wsCRC As Worksheet
    Set wsCRC = Worksheets(1)

    With wsCRC
        LastRowInCRC = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

End Function

Sub DeleteDupRowsCRC()

    Dim wsCRC As Worksheet
    Set wsCRC = Worksheets(1)

    Dim lrowcrc As Long
    lrowcrc = LastRowInCRC

    'Debug.Print "C8:C" & lrowcrc

    With wsCRC

        .Range("C1:C" & lrowcrc).RemoveDuplicates Columns:=Array(1)

    End With

End Sub

In your code, Array(3) means that you should have at least three column in the .Range. But you only have column C there. Thus, it gives mistake. To work with Array(3), Write A1:C and it would be working.

1
votes

I think your range Invalid syntax. Please, try this below

With wsCRC
    .Range(Cells(8, 3), Cells(lrowcrc, 3)).Select
    .Range(Cells(8, 3), Cells(lrowcrc, 3)).RemoveDuplicates Columns:=1, Header:=xlYes

End With

Beside, I think you should using below

lrowcrc = LastRowInCRC

Or using this code to get last row

wsCRC.[C8].SpecialCells(xlCellTypeLastCell).Row