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!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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,270
Thanks for letting us know how it turned out...the macro recorder can be an excellent teaching & guidance tool.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,631
Messages
5,838,467
Members
430,549
Latest member
jayjay2022

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