Delete row based on value in column

[The Dude]

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!

 

[Tom Urtis]

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]

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]

Thanks for letting us know how it turned out...the macro recorder can be an excellent teaching & guidance tool.