Remove duplicates with blank cells in Column

[Aqusmacro]

Hi I am using the following code found online.
It works well to match the columns I want but if a column is empty example column 3 and 6 it will not run
thanks

Sub ZapDupes()
        Set Ws = Worksheets("transactions")       '<-- *** Change to actual sheet name ***
    With Ws.Range("A1").CurrentRegion
        .RemoveDuplicates Columns:=Array(1, 2, 4, 7), Header:=xlYes ' array is the number of total colomns in needs to check. Can remove columns not to check. If there are spaces between rows it will stop
    End With
End Sub

 

[JonRowland]

Set a range instead

Sub ZapDupes()

    Dim lastRow as long
    Dim lastCol as long
 
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Range(Cells(1, 1), Cells(lastRow, lastCol)).RemoveDuplicates Columns:=Array(1, 2, 4, 7)

End Sub

 

[Aqusmacro]

Set a range instead

Sub ZapDupes()

    Dim lastRow as long
    Dim lastCol as long
 
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
   
    Range(Cells(1, 1), Cells(lastRow, lastCol)).RemoveDuplicates Columns:=Array(1, 2, 4, 7)

End Sub

thanks but getting error in the last line run time error 5 Invalid procedure call or argument

Range(Cells(1, 1), Cells(lastRow, lastCol)).RemoveDuplicates Columns:=Array(1, 2, 4, 7)

 

[JonRowland]

Is your first row empty?

 

[Aqusmacro]

thanks but getting error in the last line run time error 5 Invalid procedure call or argument

No it is not

 

[JonRowland]

Try this in place of the original lastCol

lastCol = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

Does this work?

 

[Aqusmacro]

Try this in place of the original lastCol

lastCol = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

Does this work?

Great seems to work . The only thing is that I have data validations I copied in the columns and this seems to erase everything even the formatting any way to go around this?
Thanks

 

[Aqusmacro]

Great seems to work . The only thing is that I have data validations I copied in the columns and this seems to erase everything even the formatting any way to go around this?
Thanks

Hi Added the last 3 lines and all good, unless there is an easier way. Thanks Again for your help

Sub ZapDupes()

    Dim lastRow As Long
    Dim lastCol As Long
 
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    Range(Cells(1, 1), Cells(lastRow, lastCol)).RemoveDuplicates Columns:=Array(1, 2, 4, 6, 7, 9, 10)
    
    Sheets("transactions").Range("A24000:O24000").Copy
    Sheets("transactions").Range("A24001:O" & lastRow).PasteSpecial xlPasteFormats
    Sheets("transactions").Range("A24001:O" & lastRow).PasteSpecial xlPasteValidation
    
    End Sub

 

[JonRowland]

I'm slightly confused. Are you wanting to remove duplicates from the first row onwards for from row 24000?

The lines you added rely on your formatting being held in A-0 24000, which if delete could be lost.

I'm sure there's a better way but I'd probably look at coding the formatting as a separate procedurce and then call that so at lease you know your all rows have the correct formatting.

 

[Aqusmacro]

I'm slightly confused. Are you wanting to remove duplicates from the first row onwards for from row 24000?

The lines you added rely on your formatting being held in A-0 24000, which if delete could be lost.

I'm sure there's a better way but I'd probably look at coding the formatting as a separate procedurce and then call that so at lease you know your all rows have the correct formatting.

Hi this is being used for bank transactions and I have over 28000k entries already therefore no need to go back to row 1.
The formatting seems to work so far since rows 0 to 28000 are not moving it is only what I put from now on that I need to check for duplicates and reformat.
The reason I wanted to remove duplicates is so when I download my bank transactions I don't have to look at the last date downloaded, I download always last month and I don't get double entries even if I download them every week or sooner. Been testing and so far works.
Your help on removing duplicates was greatly appreciated.