using a macro to delete

RolanDoobies

Board Regular
Joined
Aug 28, 2002
Messages
99
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
Aug 28, 2002
Messages
99

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
Aug 28, 2002
Messages
99

ADVERTISEMENT

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.
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,020
Latest member
LongDoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top