using a macro to delete

[RolanDoobies]

im using vlookup to gather data from a table. once the info is collected, i want to delete it from the table i got it from. is there a way to write a macro to do this? Ive tried, but the macro refers to the first row of text that i put into the macro every time i try it... im new to macros, so be gentle.

is there a way to select the rows of data,from the newly created vlookup and have them deleted from the original table?

 

[PaddyD]

If you're going to delete the values from the original table, your vlookup's will fall over! One way round this would be to copy | paste special...values before you did the delete, but it probably makes more sense just to do the whole thing in a macro, including the initial lookup.

You'll need to post a lot more details about how your data is set up...

Paddy

 

[RolanDoobies]

Paddy, can I email you the sheet and show you what my problem is?

 

[PaddyD]

No - my VBA is crap!

If you post more details here - where your data is, what the sheet names are,what columns you want returned, what the lookup keys are etc, I'm sure you'll get some help, or maybe someone else will offer to take a look...

Paddy

 

[RolanDoobies]

Ok,
I have a sheet with 2500 rows of data and 24 columns of data. Mixed in the 2500 rows are anywhere from 10 to 100 rows that need to be taken out. I can import column b from an external source to get the numbers i need to do the vlookup on. i understand how to set the multiple column criteria, but it doesnt seem to work. I need vlookup to bring back data in all 24 columns, but on some of the rows, the column will be blank, but i still need the column there.

Once i get the vlookup to copy the 10 to 100 rows i need, i want to delete the original lines from the table. There has to be an easier way than doing a query to find the row, then manually deleting it.

any help is greatly appreciated.

 

[PaddyD]

One (non-VBA) way that would count as relatively easy - don't bring back all the rows of data in the vlookup. Rather, flag the rows in the main table if there is a match, filter on the flag. Copy & paste special values to get the data you want then delete.

Paddy

 

[RolanDoobies]

Good Idea Paddy
can you tell me how to flag? I know how to filter, but im not sure how to find from 10 to 100 rows.

again, i can import column b(only 1 column out of 24) that will assist in finding the data in the table.

 

[PaddyD]

filter on Yes:

Book7
	A	B	C	D
1	LookupValues		Data	Flag
2	a		a	Yes
3	b		b	Yes
4	e		c
5	f		d
6			e	Yes
7			f	Yes
8			g
9
10
11
Sheet1

Paddy

 

[RolanDoobies]

Paddy,
Thank you so much. You just saved me several hours of work per month.

Mike