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

change col = 10 to refer to your column with the formulas/country codes.


Sub CleanUpData()
Dim rng as Range, col as Column
col = 10
' first, delete all error rows for errors in column J
On error resume next
columns(col).Specialcells(xlFormulas,xlErrors).ent ireRow.Delete
On error goto 0
do
if not rng is nothing then
rng.EntireRow.Delete
end if
Set rng = columns(col).Find(What:="---", _
After:=Range("J1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
loop while not rng is nothing
end Sub

--
Regards,
Tom Ogilvy

"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 Tom,

I get a compile error with this code. I am not very "great" with VB.

Rgds
Steve


"Tom Ogilvy" wrote:

change col = 10 to refer to your column with the formulas/country codes.


Sub CleanUpData()
Dim rng as Range, col as Column
col = 10
' first, delete all error rows for errors in column J
On error resume next
columns(col).Specialcells(xlFormulas,xlErrors).ent ireRow.Delete
On error goto 0
do
if not rng is nothing then
rng.EntireRow.Delete
end if
Set rng = columns(col).Find(What:="---", _
After:=Range("J1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
loop while not rng is nothing
end Sub

--
Regards,
Tom Ogilvy

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

Just a typo.


Sub CleanUpData()
Dim rng As Range, col As Long
col = 10
' first, delete all error rows for errors in column J
On Error Resume Next
Columns(col).SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
Do
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
Set rng = Columns(col).Find(What:="---", _
After:=Range("J1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Loop While Not rng Is Nothing
End Sub

--
Regards,
Tom Ogilvy

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

I get a compile error with this code. I am not very "great" with VB.

Rgds
Steve


"Tom Ogilvy" wrote:

change col = 10 to refer to your column with the formulas/country codes.


Sub CleanUpData()
Dim rng as Range, col as Column
col = 10
' first, delete all error rows for errors in column J
On error resume next
columns(col).Specialcells(xlFormulas,xlErrors).ent ireRow.Delete
On error goto 0
do
if not rng is nothing then
rng.EntireRow.Delete
end if
Set rng = columns(col).Find(What:="---", _
After:=Range("J1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
loop while not rng is nothing
end Sub

--
Regards,
Tom Ogilvy

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