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.Delete
Set rng = Nothing

End Sub



--
HTH

Bob Phillips

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

"Steve" <(E-Mail Removed)> wrote in message
news:B88DA50F-C236-473C-BB80-(E-Mail Removed)...
> 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.Delete
> Set rng = Nothing
>
> End Sub
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Steve" <(E-Mail Removed)> wrote in message
> news:B88DA50F-C236-473C-BB80-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:7BE054CB-F5E5-46DD-98C2-(E-Mail Removed)...
> 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.Delete
> > Set rng = Nothing
> >
> > End Sub
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Steve" <(E-Mail Removed)> wrote in message
> > news:B88DA50F-C236-473C-BB80-(E-Mail Removed)...
> > > 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!!!
> > > > >
> > > > >
> >
> >
> >