Dim res as Variant, rng as Range
res = InputBox("Enter a number")
if res = "" then
msgbox "No entry"
exit sub
End if
if not isnumeric(res) then
msgbox "You must enter a number"
Else
res = clng(res)
set rng = columns(1).find(res)
if not rng is nothing then
rng.Resize(1,5).Clearcontents
Else
msgbox res & " not found"
End if
End if
set rng = cells(rows.count,1).End(xlup)(2)
rng.Select

--
Regards,
Tom Ogilvy

"Tompy" wrote in message
...

I will be honest my code writting is very basic, and mostly copied off
other people, or picked up from recorded macro's. Basically i am
looking to do something pretty clever, but i have no idea where to
start. Can anyone write a code to do the following for me?

As it stands i have a macro copying data into cells A:E. At the end of
the macro i have the code - Offset.(1, 0) in order to move down a row
each time, thus creating an extending list of data.

What i am looking to do is enter a reference number into a cell, then
the macro to look up this refence number and find it in column A,
select it, then clear contents of this row for the cells A:E only.
After that i need to fnd the very last piece of data in column A
(remembering there will now be blanks in the list)and then move down 1
cell, thus returning to the start point so that more data can be copied
in from the first macro.

As i said, if anyone knows how to do this i will be really grateful.

Tompy


--
Tompy
------------------------------------------------------------------------
Tompy's Profile:
http://www.excelforum.com/member.php...o&userid=14732
View this thread: http://www.excelforum.com/showthread...hreadid=263526