Macro to delete duplicate rows but leave column A

[JadonR]

I'm using the following macro to delete duplicate rows (keeps the first value, then removes all duplicates after). However, I would like to keep column A untouched. So when the macro runs, the entire duplicate row except the cell in column A should be deleted. Is this possible?

Sub test()
    Dim r As Range, txt As String
    With CreateObject("Scripting.Dictionary")
Again:
        For Each r In Range("Q1", Range("Q" & Rows.Count).End(xlUp))
            If Not .exists(r.Value) Then
                .Add r.Value, Nothing
            Else
                txt = txt & "," & r.Address(0, 0)
                If Len(txt) > 245 Then
                    Range(Mid(txt, 2)).EntireRow.Delete
                    .RemoveAll
                    txt = Empty: GoTo Again
                End If
            End If
        Next
    End With
    If Len(txt) Then Range(Mid(txt, 2)).EntireRow.Delete
End Sub

 

[AlphaFrog]

Maybe try something like this...

Intersect(Range(Mid(txt, 2)).EntireRow, Columns("B:IV")).Delete

You realize that the data in column A will no longer be in the same rows as the rest of the data after the macro deletes.

 

[JadonR]

Yes I do realize that. I only have image buttons (for macros) in Col A and it re-arranges those buttons every time the macro deletes any rows under them.

This row appears twice in the macro:
Range(Mid(txt, 2)).EntireRow.Delete

I assume I need to replace that with your's in both places?

 

[AlphaFrog]

I assume I need to replace that with your's in both places?

Yes

 

[AlphaFrog]

I only have image buttons (for macros) in Col A and it re-arranges those buttons every time the macro deletes any rows under them.

You could also right-click on those buttons and select Format Control from the pop-up menu. On the Properties tab, select Don't move or size with cells.

 

[JadonR]

Thanks, your modification works perfectly. Good tip on setting them not to move or size with the cells. Even better, would be if they could stay in view (float), when I scroll down/up. The macro buttons go pretty much the whole distance down the left side, so expanding row 1 and freezing wouldn't really be an option.

 

[AlphaFrog]

Even better, would be if they could stay in view (float), when I scroll down/up. The macro buttons go pretty much the whole distance down the left side.

You're welcome. I'm glad it worked.

Instead of buttons in column A, you could make a custom toolbar (right-click on any toolbar and select Customize then select New) and park it on the left side.

Create a custom toolbar