Hi,
I need a macro that check the values of a column (let's say B) which has
links to another sheet. in case the cell is equal to "#N/A" I want to delete
the whole row.

I have a macro that works fine if the cell is empty (see below) :
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i,"B") Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1,Rows(i))
End If
end If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub


--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi,
I need a macro that check the values of a column (let's say B) which has
links to another sheet. in case the cell is equal to "#N/A" I want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see below) :
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then
..Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column
A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's say B) which has
links to another sheet. in case the cell is equal to "#N/A" I want to delete
the whole row.

I have a macro that works fine if the cell is empty (see below) :
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

I have tried both of the methods that you have suggested but unfortuantely no
one works.

The one from Bob returns me Compile Error: Syntax error and it gets stuck
while the one.

and yours it works only partially, meaning that it deletes some rows but not
all and it deletes also the ones with the relevant information i want to keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's say B) which has
links to another sheet. in case the cell is equal to "#N/A" I want to delete
the whole row.

I have a macro that works fine if the cell is empty (see below) :
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

A couple questions for you:

1) Are the cells you are trying use to delete the rows actually the
"#N/A" error, or do you want to delete all errors.

2) What kind of values were in the cells that were deleted that weren't
suppose to be removed

Sandy

massi wrote:
I have tried both of the methods that you have suggested but unfortuantely no
one works.

The one from Bob returns me Compile Error: Syntax error and it gets stuck
while the one.

and yours it works only partially, meaning that it deletes some rows but not
all and it deletes also the ones with the relevant information i want to keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's say B) which has
links to another sheet. in case the cell is equal to "#N/A" I want to delete
the whole row.

I have a macro that works fine if the cell is empty (see below) :
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i, "B")) Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1, Rows(i))
End If
End If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub

I think Sandy's doesn't work because he is going top-down, and the pointers
get out of sync, which is why I went bottom up.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
I have tried both of the methods that you have suggested but unfortuantely
no
one works.

The one from Bob returns me Compile Error: Syntax error and it gets stuck
while the one.

and yours it works only partially, meaning that it deletes some rows but
not
all and it deletes also the ones with the relevant information i want to
keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's say B) which
has
links to another sheet. in case the cell is equal to "#N/A" I want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see below) :
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a error in
the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then
..Rows(Lrow).Delete
'This will delete each row with the Value "ron" in
Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

I agree with you Bob, yours code is def the one to go with...

Sandy


Bob Phillips wrote:
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i, "B")) Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1, Rows(i))
End If
End If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub

I think Sandy's doesn't work because he is going top-down, and the pointers
get out of sync, which is why I went bottom up.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
I have tried both of the methods that you have suggested but unfortuantely
no
one works.

The one from Bob returns me Compile Error: Syntax error and it gets stuck
while the one.

and yours it works only partially, meaning that it deletes some rows but
not
all and it deletes also the ones with the relevant information i want to
keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's say B) which
has
links to another sheet. in case the cell is equal to "#N/A" I want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see below) :
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a error in
the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then
.Rows(Lrow).Delete
'This will delete each row with the Value "ron" in
Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

I couldn't reply earlier as I had the i-net down. At the end I manage to find
a solution.:

'-------------------------------------------------------------------------------------------
' this part transform the links in normal test and substitute #N/A with
blank cel
'-------------------------------------------------------------------------------------------
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

'--------------------------------------------------------
'and this one remove all the blank cells
'--------------------------------------------------------
Sub Macro02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b1:b10000") '<<====== CHANGE to suit

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub

it might not be a masterpiece of VB but it works fine. It was the first time
I putting my hands on a macro...

now I need to apply this macro to different files. What I have done at the
moment is:
I have a blank excel file with the macro recorded. Then I open the files I
need to work on and I run the macro from tools/macro. Is there a finer way to
make this macro working for the files? Other people should work with this
file and I was hoping to find a better solution.

cheers




Thanks for your help




"Sandy" wrote:

I agree with you Bob, yours code is def the one to go with...

Sandy


Bob Phillips wrote:
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i, "B")) Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1, Rows(i))
End If
End If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub

I think Sandy's doesn't work because he is going top-down, and the pointers
get out of sync, which is why I went bottom up.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
I have tried both of the methods that you have suggested but unfortuantely
no
one works.

The one from Bob returns me Compile Error: Syntax error and it gets stuck
while the one.

and yours it works only partially, meaning that it deletes some rows but
not
all and it deletes also the ones with the relevant information i want to
keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's say B) which
has
links to another sheet. in case the cell is equal to "#N/A" I want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see below) :
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a error in
the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then
.Rows(Lrow).Delete
'This will delete each row with the Value "ron" in
Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

Look at creating an addin.


http://support.microsoft.com/default.aspx?kbid=211563
How to create an add-in file in Excel 2000


--
HTH

Bob Phillips

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

"massi" wrote in message
...
I couldn't reply earlier as I had the i-net down. At the end I manage to
find
a solution.:


'---------------------------------------------------------------------------
----------------
' this part transform the links in normal test and substitute #N/A with
blank cell

'---------------------------------------------------------------------------
----------------
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

'--------------------------------------------------------
'and this one remove all the blank cells
'--------------------------------------------------------
Sub Macro02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b1:b10000") '<<====== CHANGE to suit

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub

it might not be a masterpiece of VB but it works fine. It was the first
time
I putting my hands on a macro...

now I need to apply this macro to different files. What I have done at the
moment is:
I have a blank excel file with the macro recorded. Then I open the files I
need to work on and I run the macro from tools/macro. Is there a finer way
to
make this macro working for the files? Other people should work with this
file and I was hoping to find a better solution.

cheers




Thanks for your help




"Sandy" wrote:

I agree with you Bob, yours code is def the one to go with...

Sandy


Bob Phillips wrote:
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i, "B")) Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1, Rows(i))
End If
End If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub

I think Sandy's doesn't work because he is going top-down, and the
pointers
get out of sync, which is why I went bottom up.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
I have tried both of the methods that you have suggested but
unfortuantely
no
one works.

The one from Bob returns me Compile Error: Syntax error and it gets
stuck
while the one.

and yours it works only partially, meaning that it deletes some rows
but
not
all and it deletes also the ones with the relevant information i
want to
keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's say B)
which
has
links to another sheet. in case the cell is equal to "#N/A" I
want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see below)
:
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a
error in
the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then
.Rows(Lrow).Delete
'This will delete each row with the Value "ron"
in
Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

Hi Bob,
good afternoon,
I have creeated the addin no problem but how do i use it?
sorry but this is all new for me...
Massimo

"Bob Phillips" wrote:

Look at creating an addin.


http://support.microsoft.com/default.aspx?kbid=211563
How to create an add-in file in Excel 2000


--
HTH

Bob Phillips

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

"massi" wrote in message
...
I couldn't reply earlier as I had the i-net down. At the end I manage to
find
a solution.:


'---------------------------------------------------------------------------
----------------
' this part transform the links in normal test and substitute #N/A with
blank cell

'---------------------------------------------------------------------------
----------------
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

'--------------------------------------------------------
'and this one remove all the blank cells
'--------------------------------------------------------
Sub Macro02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b1:b10000") '<<====== CHANGE to suit

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub

it might not be a masterpiece of VB but it works fine. It was the first
time
I putting my hands on a macro...

now I need to apply this macro to different files. What I have done at the
moment is:
I have a blank excel file with the macro recorded. Then I open the files I
need to work on and I run the macro from tools/macro. Is there a finer way
to
make this macro working for the files? Other people should work with this
file and I was hoping to find a better solution.

cheers




Thanks for your help




"Sandy" wrote:

I agree with you Bob, yours code is def the one to go with...

Sandy


Bob Phillips wrote:
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i, "B")) Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1, Rows(i))
End If
End If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub

I think Sandy's doesn't work because he is going top-down, and the
pointers
get out of sync, which is why I went bottom up.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
I have tried both of the methods that you have suggested but
unfortuantely
no
one works.

The one from Bob returns me Compile Error: Syntax error and it gets
stuck
while the one.

and yours it works only partially, meaning that it deletes some rows
but
not
all and it deletes also the ones with the relevant information i
want to
keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's say B)
which
has
links to another sheet. in case the cell is equal to "#N/A" I
want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see below)
:
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a
error in
the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then
.Rows(Lrow).Delete
'This will delete each row with the Value "ron"
in
Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

You then distribute the addin to the other desktops, and they install it,
ToolsAddinsBrowse

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi Bob,
good afternoon,
I have creeated the addin no problem but how do i use it?
sorry but this is all new for me...
Massimo

"Bob Phillips" wrote:

Look at creating an addin.


http://support.microsoft.com/default.aspx?kbid=211563
How to create an add-in file in Excel 2000


--
HTH

Bob Phillips

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

"massi" wrote in message
...
I couldn't reply earlier as I had the i-net down. At the end I manage
to
find
a solution.:



'---------------------------------------------------------------------------
----------------
' this part transform the links in normal test and substitute #N/A
with
blank cell


'---------------------------------------------------------------------------
----------------
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
End Sub

'--------------------------------------------------------
'and this one remove all the blank cells
'--------------------------------------------------------
Sub Macro02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b1:b10000") '<<====== CHANGE to suit

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub

it might not be a masterpiece of VB but it works fine. It was the
first
time
I putting my hands on a macro...

now I need to apply this macro to different files. What I have done at
the
moment is:
I have a blank excel file with the macro recorded. Then I open the
files I
need to work on and I run the macro from tools/macro. Is there a finer
way
to
make this macro working for the files? Other people should work with
this
file and I was hoping to find a better solution.

cheers




Thanks for your help




"Sandy" wrote:

I agree with you Bob, yours code is def the one to go with...

Sandy


Bob Phillips wrote:
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i, "B")) Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1, Rows(i))
End If
End If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub

I think Sandy's doesn't work because he is going top-down, and the
pointers
get out of sync, which is why I went bottom up.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
I have tried both of the methods that you have suggested but
unfortuantely
no
one works.

The one from Bob returns me Compile Error: Syntax error and it
gets
stuck
while the one.

and yours it works only partially, meaning that it deletes some
rows
but
not
all and it deletes also the ones with the relevant information i
want to
keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's say
B)
which
has
links to another sheet. in case the cell is equal to "#N/A"
I
want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see
below)
:
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is
a
error in
the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then
.Rows(Lrow).Delete
'This will delete each row with the Value
"ron"
in
Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

installation is ok. I have it now in the addins list available but i don't
know how to use it. should i have a short cut in the menu or something?
what i want is: a have a few files where i want to run my macro. at the
moment i created an empty file with the macro and i keep it open. the i open
the other files when needed and i run the macro from the 1st file.

can i make the addin work like this?



"Bob Phillips" wrote:

You then distribute the addin to the other desktops, and they install it,
ToolsAddinsBrowse

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi Bob,
good afternoon,
I have creeated the addin no problem but how do i use it?
sorry but this is all new for me...
Massimo

"Bob Phillips" wrote:

Look at creating an addin.


http://support.microsoft.com/default.aspx?kbid=211563
How to create an add-in file in Excel 2000


--
HTH

Bob Phillips

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

"massi" wrote in message
...
I couldn't reply earlier as I had the i-net down. At the end I manage
to
find
a solution.:



'---------------------------------------------------------------------------
----------------
' this part transform the links in normal test and substitute #N/A
with
blank cell


'---------------------------------------------------------------------------
----------------
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
End Sub

'--------------------------------------------------------
'and this one remove all the blank cells
'--------------------------------------------------------
Sub Macro02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b1:b10000") '<<====== CHANGE to suit

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub

it might not be a masterpiece of VB but it works fine. It was the
first
time
I putting my hands on a macro...

now I need to apply this macro to different files. What I have done at
the
moment is:
I have a blank excel file with the macro recorded. Then I open the
files I
need to work on and I run the macro from tools/macro. Is there a finer
way
to
make this macro working for the files? Other people should work with
this
file and I was hoping to find a better solution.

cheers




Thanks for your help




"Sandy" wrote:

I agree with you Bob, yours code is def the one to go with...

Sandy


Bob Phillips wrote:
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i, "B")) Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1, Rows(i))
End If
End If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub

I think Sandy's doesn't work because he is going top-down, and the
pointers
get out of sync, which is why I went bottom up.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
I have tried both of the methods that you have suggested but
unfortuantely
no
one works.

The one from Bob returns me Compile Error: Syntax error and it
gets
stuck
while the one.

and yours it works only partially, meaning that it deletes some
rows
but
not
all and it deletes also the ones with the relevant information i
want to
keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's say
B)
which
has
links to another sheet. in case the cell is equal to "#N/A"
I
want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see
below)
:
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is
a
error in
the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then
.Rows(Lrow).Delete
'This will delete each row with the Value
"ron"
in
Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

I would add a button to the menu, like this

'If you put code in the appropriate workbook open event, and
'delete it in the close it will exist only for that workbook.

'Here is an example of a building a commandbar on the fly
'when you open a workbook. It adds a sub-menu to the Tools menu.

Option Explicit

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

On Error Resume Next
Application.CommandBars("Worksheet Menu
Bar").Controls("myMacroButton").Delete
On Erroro GoTo 0

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMacroButton").Delete
End Sub

'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.



--
HTH

Bob Phillips

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

"massi" wrote in message
...
installation is ok. I have it now in the addins list available but i don't
know how to use it. should i have a short cut in the menu or something?
what i want is: a have a few files where i want to run my macro. at the
moment i created an empty file with the macro and i keep it open. the i
open
the other files when needed and i run the macro from the 1st file.

can i make the addin work like this?



"Bob Phillips" wrote:

You then distribute the addin to the other desktops, and they install
it,
ToolsAddinsBrowse

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi Bob,
good afternoon,
I have creeated the addin no problem but how do i use it?
sorry but this is all new for me...
Massimo

"Bob Phillips" wrote:

Look at creating an addin.


http://support.microsoft.com/default.aspx?kbid=211563
How to create an add-in file in Excel 2000


--
HTH

Bob Phillips

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

"massi" wrote in message
...
I couldn't reply earlier as I had the i-net down. At the end I
manage
to
find
a solution.:




'---------------------------------------------------------------------------
----------------
' this part transform the links in normal test and substitute #N/A
with
blank cell



'---------------------------------------------------------------------------
----------------
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Replace What:="#N/A", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False,
_
ReplaceFormat:=False
End Sub

'--------------------------------------------------------
'and this one remove all the blank cells
'--------------------------------------------------------
Sub Macro02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b1:b10000") '<<====== CHANGE to suit

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub

it might not be a masterpiece of VB but it works fine. It was the
first
time
I putting my hands on a macro...

now I need to apply this macro to different files. What I have
done at
the
moment is:
I have a blank excel file with the macro recorded. Then I open the
files I
need to work on and I run the macro from tools/macro. Is there a
finer
way
to
make this macro working for the files? Other people should work
with
this
file and I was hoping to find a better solution.

cheers




Thanks for your help




"Sandy" wrote:

I agree with you Bob, yours code is def the one to go with...

Sandy


Bob Phillips wrote:
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i, "B")) Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1, Rows(i))
End If
End If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub

I think Sandy's doesn't work because he is going top-down, and
the
pointers
get out of sync, which is why I went bottom up.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
I have tried both of the methods that you have suggested but
unfortuantely
no
one works.

The one from Bob returns me Compile Error: Syntax error and
it
gets
stuck
while the one.

and yours it works only partially, meaning that it deletes
some
rows
but
not
all and it deletes also the ones with the relevant
information i
want to
keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's
say
B)
which
has
links to another sheet. in case the cell is equal to
"#N/A"
I
want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see
below)
:
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to
b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there
is
a
error in
the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then
.Rows(Lrow).Delete
'This will delete each row with the
Value
"ron"
in
Column A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx

thanks
Rgds
Massi

"Bob Phillips" wrote:

I would add a button to the menu, like this

'If you put code in the appropriate workbook open event, and
'delete it in the close it will exist only for that workbook.

'Here is an example of a building a commandbar on the fly
'when you open a workbook. It adds a sub-menu to the Tools menu.

Option Explicit

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

On Error Resume Next
Application.CommandBars("Worksheet Menu
Bar").Controls("myMacroButton").Delete
On Erroro GoTo 0

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMacroButton").Delete
End Sub

'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.



--
HTH

Bob Phillips

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

"massi" wrote in message
...
installation is ok. I have it now in the addins list available but i don't
know how to use it. should i have a short cut in the menu or something?
what i want is: a have a few files where i want to run my macro. at the
moment i created an empty file with the macro and i keep it open. the i
open
the other files when needed and i run the macro from the 1st file.

can i make the addin work like this?



"Bob Phillips" wrote:

You then distribute the addin to the other desktops, and they install
it,
ToolsAddinsBrowse

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi Bob,
good afternoon,
I have creeated the addin no problem but how do i use it?
sorry but this is all new for me...
Massimo

"Bob Phillips" wrote:

Look at creating an addin.


http://support.microsoft.com/default.aspx?kbid=211563
How to create an add-in file in Excel 2000


--
HTH

Bob Phillips

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

"massi" wrote in message
...
I couldn't reply earlier as I had the i-net down. At the end I
manage
to
find
a solution.:




'---------------------------------------------------------------------------
----------------
' this part transform the links in normal test and substitute #N/A
with
blank cell



'---------------------------------------------------------------------------
----------------
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Replace What:="#N/A", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False,
_
ReplaceFormat:=False
End Sub

'--------------------------------------------------------
'and this one remove all the blank cells
'--------------------------------------------------------
Sub Macro02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b1:b10000") '<<====== CHANGE to suit

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub

it might not be a masterpiece of VB but it works fine. It was the
first
time
I putting my hands on a macro...

now I need to apply this macro to different files. What I have
done at
the
moment is:
I have a blank excel file with the macro recorded. Then I open the
files I
need to work on and I run the macro from tools/macro. Is there a
finer
way
to
make this macro working for the files? Other people should work
with
this
file and I was hoping to find a better solution.

cheers




Thanks for your help




"Sandy" wrote:

I agree with you Bob, yours code is def the one to go with...

Sandy


Bob Phillips wrote:
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i, "B")) Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1, Rows(i))
End If
End If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub

I think Sandy's doesn't work because he is going top-down, and
the
pointers
get out of sync, which is why I went bottom up.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
I have tried both of the methods that you have suggested but
unfortuantely
no
one works.

The one from Bob returns me Compile Error: Syntax error and
it
gets
stuck
while the one.

and yours it works only partially, meaning that it deletes
some
rows
but
not
all and it deletes also the ones with the relevant
information i
want to
keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's
say
B)
which
has
links to another sheet. in case the cell is equal to
"#N/A"
I
want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see
below)
:
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to
b10000

"Bob Phillips" wrote:

I would add a button to the menu, like this

'If you put code in the appropriate workbook open event, and
'delete it in the close it will exist only for that workbook.

'Here is an example of a building a commandbar on the fly
'when you open a workbook. It adds a sub-menu to the Tools menu.

Option Explicit

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

On Error Resume Next
Application.CommandBars("Worksheet Menu
Bar").Controls("myMacroButton").Delete
On Erroro GoTo 0

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("myMacroButton").Delete
End Sub

'To add this, go to the VB IDE (ALT-F11 from Excel), and in
'the explorer pane, select your workbook. Then select the
'ThisWorkbook object (it's in Microsoft Excel Objects which
'might need expanding). Double-click the ThisWorkbook and
'a code window will open up. Copy this code into there,
'changing the caption and action to suit.

'This is part of the workbook, and will only exist with the
'workbook, but will be available to anyone who opens the
'workbook.



--
HTH

Bob Phillips

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

"massi" wrote in message
...
installation is ok. I have it now in the addins list available but i don't
know how to use it. should i have a short cut in the menu or something?
what i want is: a have a few files where i want to run my macro. at the
moment i created an empty file with the macro and i keep it open. the i
open
the other files when needed and i run the macro from the 1st file.

can i make the addin work like this?



"Bob Phillips" wrote:

You then distribute the addin to the other desktops, and they install
it,
ToolsAddinsBrowse

--
HTH

Bob Phillips

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

"massi" wrote in message
...
Hi Bob,
good afternoon,
I have creeated the addin no problem but how do i use it?
sorry but this is all new for me...
Massimo

"Bob Phillips" wrote:

Look at creating an addin.


http://support.microsoft.com/default.aspx?kbid=211563
How to create an add-in file in Excel 2000


--
HTH

Bob Phillips

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

"massi" wrote in message
...
I couldn't reply earlier as I had the i-net down. At the end I
manage
to
find
a solution.:




'---------------------------------------------------------------------------
----------------
' this part transform the links in normal test and substitute #N/A
with
blank cell



'---------------------------------------------------------------------------
----------------
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Replace What:="#N/A", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False,
_
ReplaceFormat:=False
End Sub

'--------------------------------------------------------
'and this one remove all the blank cells
'--------------------------------------------------------
Sub Macro02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b1:b10000") '<<====== CHANGE to suit

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub

it might not be a masterpiece of VB but it works fine. It was the
first
time
I putting my hands on a macro...

now I need to apply this macro to different files. What I have
done at
the
moment is:
I have a blank excel file with the macro recorded. Then I open the
files I
need to work on and I run the macro from tools/macro. Is there a
finer
way
to
make this macro working for the files? Other people should work
with
this
file and I was hoping to find a better solution.

cheers




Thanks for your help




"Sandy" wrote:

I agree with you Bob, yours code is def the one to go with...

Sandy


Bob Phillips wrote:
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i, "B")) Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1, Rows(i))
End If
End If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub

I think Sandy's doesn't work because he is going top-down, and
the
pointers
get out of sync, which is why I went bottom up.

--
HTH

Bob Phillips

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

"massi" wrote in message
...
I have tried both of the methods that you have suggested but
unfortuantely
no
one works.

The one from Bob returns me Compile Error: Syntax error and
it
gets
stuck
while the one.

and yours it works only partially, meaning that it deletes
some
rows
but
not
all and it deletes also the ones with the relevant
information i
want to
keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
Hi,
I need a macro that check the values of a column (let's
say
B)
which
has
links to another sheet. in case the cell is equal to
"#N/A"
I
want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see
below)
:
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to
b10000