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

 

[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 I ran into a little snag. If I have something written in the last column memo on the last line I get an runtime error 13 type mismatch in the following line
lastCol = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Ii last line is empty all is good.

see screenshot sorry just installed office 365 and having trouble with installing Xl2BB getting a this file is not supported in protected view.

 

[Aqusmacro]

Hi I ran into a little snag. If I have something written in the last column memo on the last line I get an runtime error 13 type mismatch in the following line
lastCol = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Ii last line is empty all is good.

see screenshot sorry just installed office 365 and having trouble with installing Xl2BB getting a this file is not supported in protected view.

Think I fixed it If I remove
Dim lastRow As Long
Dim lastCol As Long

then it seems to work unless I do a copy paste of cell contents from a previously imported data in my memo column then it will give same error. But That I don't mind as hardly need to do that.
If I don't remove dim lastrow...then I get error as previously stated. If I do and write fresh data in all my columns including Memo Then it works.
I will do more testing but for now I will consider it resolved

 

[Peter_SSs]

just installed office 365 and having trouble with installing Xl2BB getting a this file is not supported in protected view.

See solution suggestions here: XL2BB Icons greyed out