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