mardi 19 septembre 2017

Excel VBA - Removing duplicate rows that contain checkboxes (duplicates from a single column)

I have a spreadsheet with checkboxes in one of the columns and I would like to have a macro that removes duplicate rows (with checkboxes for those rows).

I currently have the following code which does exactly what I need, however checkboxes do not delete with rows. I am using excel 2010 with form control checkboxes which are set up to "move but don't size with cells".

Could someone help me add a line of code which would delete the checkbox when deleting a row.

Here is my current macro:

Sub CleanSheet()

Dim LastRowcheck As Long, n1 As Long
Dim DelRange As Range

With Worksheets("Scale Costs")
    LastRowcheck = .Range("A" & .Rows.Count).End(xlUp).Row

    For n1 = 1 To LastRowcheck
        If .Cells(n1, 1).Value = Cells(n1 + 1, 1).Value Then
            If DelRange Is Nothing Then
                Set DelRange = .Rows(n1)
            Else
                Set DelRange = Union(DelRange, .Rows(n1))
            End If
        End If
    Next n1

    If Not DelRange Is Nothing Then DelRange.Delete
End With
End Sub

I found a line of code online that can delete the checkbox in the selected cell, but I'm not sure how to add it to the macro I have.




Aucun commentaire:

Enregistrer un commentaire