Delete rows based on criteria

[MOB]

Column A contains entries such as DE0001, DE0002, DE0003 and so on, can be duplicates.

Column K can be empty or contain any entry, text or numeric.

I need a bit of code that;

- rows 6 to "lastrow", if column K is empty, delete the row, unless the value in column A is unique - the row should remain in those instances
- if the value in column A is not unique and column K is empty, delete any of the excess rows, leaving one unique instance remaining

Is there a simple bit of VBA that would achieve this?

TIA

 

[shknbk2]

Try this. The code works its way up from the bottom to make it a little simpler. If you want it to work top down, then another variable will have to be used to keep track of the row numbers.

Sub deleteEmptyK()
    Dim i As Long, l as Long
    
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 6 Step -1
        If Cells(i, 11).Value = "" Then
            l = Application.WorksheetFunction.CountIf(Columns(1), Cells(i, 1).Value)
            If l > 1 Then
                Rows(i).Delete
            End If
        End If
    Next i
End Sub

 

[MOB]

Thanks for the response - I get a run time 1004 error, "Delete method of range class failed" and this line of code is highlighted;

Rows(i).Delete

I'm not too strong with VBA when it doesn't work!

Thanks

 

[Peter_SSs]

About how many rows of data do you have altogether?

 

[MOB]

Approximately 100-150 usually

 

[MOB]

At the moment it is a fairly simple monthly spreadsheet that users have to manually delete rows from based on my criteria above in order to create the next month's spreadsheet, and of course I want to automate it as there are 60 of these spreadsheets.....................

 

[MOB]

Thanks for the response - I get a run time 1004 error, "Delete method of range class failed" and this line of code is highlighted;

Rows(i).Delete

I'm not too strong with VBA when it doesn't work!

Thanks

Actually ignore me, the sheet was protected!! I'll have a play and report back.

 

[MOB]

Seems to work perfectly, many thanks!!