delete duplicate rows, delete empty rows

hello!

is there a macro to delete all duplicate rows?
is there a macro to delete all empty rows?

(seperate macros)

thanks!

Hi Tom,

Im happy to learn from you again and again. The above code of yours and your knowledge deserves a rep :-)


Thanks,

Bonny Tycoon



**If I was able to help please click the small star icon at the bottom left of my post **

Originally Posted by arlu1201

For duplicate rows, are there just 2 rows duplicating or more?

there may be more duplicates

I didnt upload anything, because it has to be universal solution

Originally Posted by arlu1201

Based on what conditions should the duplicate rows be deleted? I mean, which are the fields that should be checked for duplicates?

Umm, just when two or more rows are exactly the same, to be more precise, have the same text, but then leave intact the first and delete all subsequent duplicates

Originally Posted by arlu1201

Since you are attempting an universal solution, is there any particular range or can that be changing each time?

the range is from the first not null cell to the last not null cell

This is the code I usually use. It asks which column to check for duplicates and blanks.

Sub DeleteDuplicates()
Dim colbox As String
colbox = InputBox(Prompt:="Column to search for duplicates:", Title:="Column", Default:="A")
colstring = colbox & ":" & colbox
checkrow = 2

Application.ScreenUpdating = False

Do Until checkrow = lastrow

lastrow = ActiveSheet.UsedRange.Rows.Count

matchcheck = Application.CountIf(Range(colstring), Cells(checkrow, colbox))
    
    If matchcheck > 1 Then
    Cells(checkrow, colbox).EntireRow.Delete
    ElseIf Cells(checkrow, colbox) = "" Then
    Cells(checkrow, colbox).EntireRow.Delete
    
    Else
    checkrow = checkrow + 1

End If

Loop

Application.ScreenUpdating = True

MsgBox ("Done deleting duplicates.")

End Sub

Not the most efficient or best way at all but I personally like it. Good Luck

Originally Posted by arlu1201

So even if a file has 20 columns of data, you want all 20 columns to be compared to each other for duplicates?

regardless of the number of columns, data will be considered as rows only