Finding Blanks in list

[Jahsonn]

I have a list of data as such.

112526 name (e-mail address removed)
254412 name (e-mail address removed)
125421 name
254412 name (e-mail address removed)
254412

The first column will always have a value in it but the
second and third columns may not have values in them. Is
it possible to have a macro run down the list and any
items which it doesnt have values in columns b and c to
copy to another sheet?

 

[Tom Ogilvy]

Dim rng as Range, rng1 as Range
set rng = Range(Cells(1,1),Cells(1,1).End(xldown))
On error resume Next
set rng1 = rng.offset(0,1).Resize(,2).SpecialCells(xlBlanks)
On Error goto 0
if not rng1 is nothing then
rng1.EntireRow.copy Destination:=Worksheets(2).Range("A1")
End if

 

[Jahsonn]

Hi Tom

Thanks for the speedy response, This works perfectly but
the problem i am having is that the cells is the sheet
that I am checking to see if they are blank actually have
formulas in them which are set to display nothing if there
is an error. Is there any way to change this so it doesnt
look at the formulas?

Thanks

 

[Tom Ogilvy]

no, you would need to loop in that case. unless you change your formulas to
=if(condition,formula,na())

so rather than be blank, they return errors. Then you could change xlblanks
to xlformulas,xlerrors