MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA help find + delete


Posted by jack in UK on December 26, 2001 12:18 PM

i have the nasty task of maintaining a database lucky in Excel in cold d in ulr ie Jack.InUK@internet.com

Buy say i want to delete all Internet enrtys...

Also the reverser, if i want to keep only Internet accts, thought Find internet and delete row,
Any ideas guys
Cheers Jack in UK


Posted by Jacob on December 26, 2001 1:21 PM

Try this

sub deleteit()

dim c
dim lastaddress

with sheets("MySheet").range("MyRange")

set c = .find.Find(What:="Internet", After:=LastAddress, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
lastaddress = c.address
if not c is nothing then
do
Rows(c.row).Select
Selection.Delete Shift:=xlUp

loop while not c is nothing and c.address <> lastaddress

else
end if

end sub

hope this helps

jacob

Posted by Jack in Uk on December 26, 2001 2:39 PM

loop while not c is nothing and c.address <> lastaddress else end if

Jacob

Thanks for taht i have two errors
1) Set C ... debugs???
2) Requests end with???

Any ideas, looks great i would love to use the code
Cheers
Jack

Posted by Jacob on December 26, 2001 4:39 PM

I forgot the end with

put end with on a new line after the end if and before end sub


Later
Jacob

loop while not c is nothing and c.address <> lastaddress else end if

Posted by Jack in UK on December 27, 2001 2:11 AM

Jacob
debugs on this line Set c = .Find.Find(Wh
Any ideas, wont run this code ???
Any ideas
Cheers
Jack I forgot the end with put end with on a new line after the end if and before end sub

loop while not c is nothing and c.address <> lastaddress else end if

Posted by Mark O'Brien on December 27, 2001 8:10 AM

Looks like you should just have:

Set c = .Find(What:=etc etc...) Jacob loop while not c is nothing and c.address <> lastaddress else end if

Posted by JACK IN UK on December 27, 2001 2:25 PM

Set c = .Find(What:=etc etc...) : Jacob loop while not c is nothing and c.address <> lastaddress else end if

Cheers for that, thats what i tried and debugs????
I have no ides why. i dont have VBA problems ebver before any other ideas, please try the code its above and ??? im lost ... would love to use it thou

Cheers again

Posted by moahmed on February 10, 2002 3:41 PM

Help me

hello sir
i need a help for to fix my vba error on microsoft word. the problem is : whenever i try to run the visual basic, the message box says: unable to crean an embedded object." it doesn't work. what should i download for to fix this message or this error. and also i cannot open the vba control toolbox and code . i don't see any of them.And if i double click on the form. it should take me to vbcode right. but it didn't. it didn't work. even if i click view menu and toolbox invisible.please sir, i have a project to deal with vba. please help me out. i will expect ur email very soon
thank u sir

Posted by moahmed on February 10, 2002 3:42 PM

Help me

hello sir
i need a help for to fix my vba error on microsoft word. the problem is : whenever i try to run the visual basic, the message box says: "unable to create an embedded object." it doesn't work. what should i download for to fix this message or this error. and also i cannot open the vba control toolbox and code . i don't see any of them.And if i double click on the form. it should take me to vbcode right. but it didn't. it didn't work. even if i click view menu and toolbox invisible.please sir, i have a project to deal with vba. please help me out. i will expect ur email very soon
thank u sir