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!!!

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!!!

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!!!

you can use the SpecialCells function to return all the cells that have
'#N/A' in them, then delete those rows?

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!!!

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!!!

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!!!

It is passed by parameter, I used 2 (B) in my example.

--
HTH

Bob Phillips

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

"Steve" wrote in message
...
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!!!