Delete row based on value in column

The Dude

New Member
Joined
May 20, 2008
Messages
4
Hi All

Once again, showing my complete ineptitude when it comes to creating macros I throw myself on the mercy of the gurus that inhabit this forum.

My problem is simple: I need a macro that will go through all the data in the D column, identify any records that DO NOT say "Student" and then delete the entire row so that I am only left with student records.

Please help!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,229
You can use the macro recorder to give you a good start at this.

Turn on the MR, then select column D (assuming your header row is row 1), and from the worksheet menu click Data > Filter > AutoFilter.

Click the down arrow in cell D1, select Custom, and in the fields provided, select Does Not Equal and "Student" respectively, and click OK.

Next, select the range in column D below the headers to one row below the last row of data, and from the worksheet menu click Edit > GoTo > Special > Visible Cells Only. Press the Delete Key.

Exit AutoFilter mode.

Finally, select column D again, and from the worksheet menu click Edit > GoTo > Special > Blanks. With those cells selected, assuming there are fewer than 8192 of them, from the worksheet menu click Edit > Delete > Entire Row > OK.

If your range is dynamic you can adjust the filter range for the last row in column D but this will get you going, to try and modify from there. If you get stuck post back.
 

The Dude

New Member
Joined
May 20, 2008
Messages
4
Hi Tom

Well, that seems to have done the trick! Sometimes you get so tied up thinking of complicated macros when really the simple option is to use the macro recorder.

Thanks for the guidance.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,229
Thanks for letting us know how it turned out...the macro recorder can be an excellent teaching & guidance tool.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,242
Messages
5,623,573
Members
415,981
Latest member
Baltwin

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