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