Deleting rows after filtering using VBA

[Lee Jeffery]

Each fortnight I receive a set of data in a spreadsheet which I need t
sort and format. So far I can format the sheet and sort and delete row
with values of <=0 in Column G.

In column A is a set of two letter codes (EA, BC, HP, etc) which ca
vary each month so the macro I recorded to apply autofiltering bomb
out when it finds one of these codes which didn't exist in the sheet
used to record the macro. It's also only deleting 1 row at a time afte
filtering. I'm not sure how to set the variable for the two lette
codes or how to delete all the rows which show after autofilterin
without manually selecting them as the row numbers are different eac
time.

I have reproduced the relevant macro code in part and I woul
appreciate some help in the right direction. Many thanks if you ca
help.
Sub Format570()
Application.ScreenUpdating = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="BC"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="EA"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="EE"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1, Criteria1:="EI"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Application.ScreenUpdating = True
End Su

 

[Don Guillett]

I found this

From: Norman Jones ([email protected])
and modified to delete whats in the line below
Exceptions = Array("bb", "dd")

Sub DeleteAllExcept() 'works
Dim Exceptions As Variant
Dim cell As Range, LastCell As Range
Dim MyDelRng As Range
Dim WB As Workbook, SH As Worksheet
Dim CountryCol As String
Dim blKeep As Boolean

Exceptions = Array("bb", "dd") '<==== CHANGE
Set WB = Workbooks("20040709.xls") '<==== CHANGE
Set SH = WB.Sheets("Sheet6") '<==== CHANGE
CountryCol = "A" '<==== CHANGE

Set LastCell = SH.Cells(Rows.Count, CountryCol).End(xlUp)
For Each cell In Range(SH.Cells(1, CountryCol), LastCell)
blKeep = False
On Error Resume Next
blKeep = Application.Match(cell.Value, Exceptions, 0)
On Error Resume Next

If blKeep Then
If MyDelRng Is Nothing Then
Set MyDelRng = cell
Else
Set MyDelRng = Union(cell, MyDelRng)
End If
End If
Next cell

If Not MyDelRng Is Nothing Then
MyDelRng.EntireRow.Delete
Else
MsgBox "No data found to delete"
End If

End Sub

 

[Lee Jeffery]

Thank you for your suggestion Don but I am unfamiliar with how to se
up the array parameters and I would appreciate some additional advice
I tried changing the areas you suggested but I am obviously doin
something wrong as nothing is deleting and I get the message box sayin
there is no data to delete.

The raw data I receive always places the two letter codes (which ar
used to identify each row of values) in column A. I want to keep an
rows which have HA, HB, HC, HE, HF, HG, HJ, HP, HM appearing in colum
A. If any other two letter codes appear in Column A, I want to delet
the entire row relating to the code.

The way I originally tackled this was to apply an autofilter and us
this to display each code I didn't want and delete the rows but, as th
range changes on each report, I needed a way to always select th
correct range of each unwanted code. This is probably a clumsy way o
going about this??

 

[Don Guillett]

I had changed it to delete what was in exceptions. Then change the line
If blKeep Then
back to what it was before I changed it
If NOT blKeep Then
and then use
Exceptions = Array("HA", "HB", etc,etc) '<==== CHANGE Case Sensetive
Now try it again.