Delete and Move Up
 

Hi...

I have 3000 records. In column B, some cells are reading #N/A. I need some
code that will delete the rows where the #N/A occours and leave no gaps. So
by the time I'm through I have say 2800 records with no gaps in the rows? Is
this possible?

Thanks

Gordon

Delete and Move Up
 

On your active row try this....


Selection.Delete Shift:=xlUp

Louis Markham

Delete and Move Up
 

Hi Gordon,

If no other error present, the you can use (save before testing)
Columns("B:B").SpecialCells(xlCellTypeFormulas,
xlErrors).EntireRow.Delete
Else loop, delete the N/A then
Columns("B:B").SpecialCells(xlblanks).EntireRow.De lete
Regards
JY

"Gordon" wrote in message
...
Hi...

I have 3000 records. In column B, some cells are reading #N/A. I need some
code that will delete the rows where the #N/A occours and leave no gaps.
So
by the time I'm through I have say 2800 records with no gaps in the rows?
Is
this possible?

Thanks

Gordon

Delete and Move Up
 

Jean...

I'm not too hot on the rules that govern the building of workable macros,
though I know how they work when I see them - bit like learning a foreign
language.

Could you wrap this up for me so it will work.

Thanks in advance.

Gordon...

"Jean-Yves" wrote:

Hi Gordon,

If no other error present, the you can use (save before testing)
Columns("B:B").SpecialCells(xlCellTypeFormulas,
xlErrors).EntireRow.Delete
Else loop, delete the N/A then
Columns("B:B").SpecialCells(xlblanks).EntireRow.De lete
Regards
JY

"Gordon" wrote in message
...
Hi...

I have 3000 records. In column B, some cells are reading #N/A. I need some
code that will delete the rows where the #N/A occours and leave no gaps.
So
by the time I'm through I have say 2800 records with no gaps in the rows?
Is
this possible?

Thanks

Gordon

Delete and Move Up
 

sub test
This use the xl function IsNA, if true, clear cell content

Dim rng as range
for each rng in range("B2",range("B2")).end(xldown)
if application.WorksheetFunction.IsNA(range) then rng.clear
next
Columns("B:B").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
end sub
Nice WE (*I go home now*)
Regards
JY
"Gordon" wrote in message
...
Jean...

I'm not too hot on the rules that govern the building of workable macros,
though I know how they work when I see them - bit like learning a foreign
language.

Could you wrap this up for me so it will work.

Thanks in advance.

Gordon...

"Jean-Yves" wrote:

Hi Gordon,

If no other error present, the you can use (save before testing)
Columns("B:B").SpecialCells(xlCellTypeFormulas,
xlErrors).EntireRow.Delete
Else loop, delete the N/A then
Columns("B:B").SpecialCells(xlblanks).EntireRow.De lete
Regards
JY

"Gordon" wrote in message
...
Hi...

I have 3000 records. In column B, some cells are reading #N/A. I need
some
code that will delete the rows where the #N/A occours and leave no
gaps.
So
by the time I'm through I have say 2800 records with no gaps in the
rows?
Is
this possible?

Thanks

Gordon