Deleting rows based on a cell value
 

Hi,

I need to delete all the rows in a worksheet which match a particular
value in a column. Below is a sample of the sheet:
Partner# Name Customer#

A_NOVATION ABBOTT NOVATION 26967
B_NOVATION BOSTON NOVATION 26967
A_PREMIER ABBOTT PREMIER 26968
B_PREMIER BOSTON PREMIER 26968
A_BROADLAN ABBOTT BROADLANE 26969
B_BROADLAN BOSTON BROADLANE 26969
A_BROADLAN ABBOTT BROADLANE 26971
B_BROADLAN BOSTON BROADLANE 26971
A_BROADLAN ABBOTT BROADLANE 26972
B_BROADLAN BOSTON BROADLANE 26972
A_NOVATION ABBOTT NOVATION 26973
B_NOVATION BOSTON NOVATION 26973
MEDASSETS MEDASSETS HSCA INC 26974
A_NOVATION ABBOTT NOVATION 26975
B_NOVATION BOSTON NOVATION 26975
A_HEALTHTR ABBOTT HEALTHTRUST 26977
B_HEALTHTR BOSTON HEALTHTRUST 26977

So basically i want to delete all the rows for which column 1 has a
value starting with A_.
Can anyone help me with this?

Thanks

Deleting rows based on a cell value
 

You can try this for column A
A1 = the header cell

Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range

DeleteValue = "A"
With ActiveSheet
.Range("A:A").AutoFilter Field:=1, Criteria1:=DeleteValue & "*"
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
End With
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



wrote in message ups.com...
Hi,

I need to delete all the rows in a worksheet which match a particular
value in a column. Below is a sample of the sheet:
Partner# Name Customer#

A_NOVATION ABBOTT NOVATION 26967
B_NOVATION BOSTON NOVATION 26967
A_PREMIER ABBOTT PREMIER 26968
B_PREMIER BOSTON PREMIER 26968
A_BROADLAN ABBOTT BROADLANE 26969
B_BROADLAN BOSTON BROADLANE 26969
A_BROADLAN ABBOTT BROADLANE 26971
B_BROADLAN BOSTON BROADLANE 26971
A_BROADLAN ABBOTT BROADLANE 26972
B_BROADLAN BOSTON BROADLANE 26972
A_NOVATION ABBOTT NOVATION 26973
B_NOVATION BOSTON NOVATION 26973
MEDASSETS MEDASSETS HSCA INC 26974
A_NOVATION ABBOTT NOVATION 26975
B_NOVATION BOSTON NOVATION 26975
A_HEALTHTR ABBOTT HEALTHTRUST 26977
B_HEALTHTR BOSTON HEALTHTRUST 26977

So basically i want to delete all the rows for which column 1 has a
value starting with A_.
Can anyone help me with this?

Thanks