Macro to Delete Certain Rows

[Doogs2009]

Hi
I have a large worksheet of data with 4 columns of data. There are duplicated items in Column A that i want to delete, leaving only 1 unique item. I have placed a formula in Column B to mark which items need to be deleted if they are duplicates, now I need some sot of macro or VBA code to go through and delete all rows with the word DEL in column B. Below is a sample of my data
COLUMN A
<TABLE style="WIDTH: 315pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=419><COLGROUP><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6802" width=186><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; WIDTH: 140pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20 width=186>SSA Glenorchy</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>DEL</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>S51</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 79pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=105>Fortune</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>SSA Glenorchy</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">DEL</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">S51</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Fortune</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>SSA Glenorchy</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">DEL</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">S51</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Fortune</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>SSA Glenorchy</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">DEL</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">S51</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Fortune</TD></TR></TBODY></TABLE>

Can someone please tell me the VBA code to go through my worksheet and delete the rows of all occurrences of the DEL. I need the whole row deleted where this happens.

The total worksheet size at present is approximately 3000 rows.
Any help would be greatly appreciated. Thanks


Thanks

 

[Wee Kuang]

Hello. Try this:
(I assume the last set of data entry is determined by Column A)

Sub DeleteRows()

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For n = 1 To LastRow

    If Range("B" & n) = "DEL" Then
    Rows(n).EntireRow.Delete
    n = n - 1
    End If

Next n

End Sub

 

[Michael M]

When deleting rows it's advisable AND quicker to start at the lastrow and work up as opposed to starting at the top

Sub DeleteRows()
dim lastrow as long, n as long
Application.screenupdating=false
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For n = LastRow to 1 step -1
    If Range("B" & n) = "DEL" Then Rows(n).Delete
Next n
Application.screenupdating=true
End Sub

 

[Doogs2009]

Hello Wee
Ok, i copied and pasted your VBA code into This Workbook.
The DEL is in Column B and there are some blanks for the entries i want to keep that are not duplicates.
When i ran the Macro i got the following error:-

Run Time Error '13'
Type Mismatch.

What does this mean please?

Thanks

 

[Doogs2009]

Michael M

I tried your code also.
Column B which used to have DEL entries have now been replaced with "REF#" errors, and the first line entry in Column A is still there and is a duplicate of the one below it. Does the code need tweeking slightly do you think?

Many thanks
Craig

 

[Wee Kuang]

When deleting rows it's advisable AND quicker to start at the lastrow and work up as opposed to starting at the top

Sub DeleteRows()
dim lastrow as long, n as long
Application.screenupdating=false
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For n = LastRow to 1 step -1
    If TypeName(Range("B" & n).Value) = "Error" Then Exit Sub 
    If Range("B" & n) = "DEL" Then Rows(n).Delete
Next n
Application.screenupdating=true
End Sub

Try adding that line mark out in green. Not sure if it helps though.

 

[Doogs2009]

Thanks Wee

Your mods worked, and i reran the code it worked well.

Cheers'

Craig

 

[Wee Kuang]

You're welcomed.