Hi Bob,

I'm trying to see where it chooses the right column to perform the filter!
Could you poss put some comments in to explain to a dumba$$ like me?

Rgds
Steve


"Bob Phillips" wrote:

Sub deletedata()
DeleteByAutoFilter 2, "--"
DeleteByAutoFilter 2, "#N/A"

End Sub


Private Sub DeleteByAutoFilter(Col As Long, criteria As String)
Dim iLastRow As Long
Dim i As Long
Dim rng As Range

Rows(1).Insert
Cells(1, Col).Value = "temp"
iLastRow = Cells(Rows.Count, Col).End(xlUp).Row
Set rng = Cells(1, Col).Resize(iLastRow)
rng.AutoFilter field:=1, Criteria1:=criteria
rng.SpecialCells(xlCellTypeVisible).EntireRow.Dele te
Set rng = Nothing

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Steve" wrote in message
...
Hi Philip,

Thanks for that, I can see potential... Although I have 66,000 rows of
data
to deal with! I'll take a look, but if anyone has any other ideas it
would
be appreciated.

Steve

"Philip" wrote:

Hi,

Using the SpecialCells function you can delete all rows that match the
criteria #N/A ...

see Ron De Bruin's excellant site on this...

http://www.rondebruin.nl/specialcells.htm

HTH

Philip
"Steve" wrote:

Hi,

I am trying to stick the only manual part of my function in to my
macro.

Once a load of data has been pasted in to a sheet, a formula in Column
J
compares dumped data and returns the country code applicable,
alternatively
it puts "---" if there is no code and a #N/A if it is not found.

I need to;
Delete all ROWs which have "---" in column J

Currently the manual way I do it is;
- Sort Ascending, so all data follows on.
- Filter on "---"
- Highlight ROW 1 to whatever, Delete Rows
- Repeat for #N/A

What would be good would be if I could automate this.
Autofilter, Custom Filter "---" OR "#N/A"
This now shows only what I want to delete, I want to delete all these
rows
(leaving header information intact).
But it mustn't delete anything in between the filtered rows obviously!

HELP!!!