Deleting rows where value in column AN = certain value

[markyc]

I have to run monthly reports for work and these can run into several thousand lines, last months 42500 lines.

I want to be able to delete rows where the value in column AN is not equal to "Aviation" or "Marine"

Please help getting bored of highlighting and deleting

[TonyJollans]

Use an AutoFilter on column AN .. Custom .. "does not equal" "Aviation" AND "does not equal" "Marine" .. delete all visible rows. Record if you need code.

[MWE]

Use an AutoFilter on column AN .. Custom .. "does not equal" "Aviation" AND "does not equal" "Marine" .. delete all visible rows. Record if you need code.
to clarify Tony's suggestion:

The Macro Recorder is a very useful tool to understand the VBA code required to perform certain operations. Everyone who develops VBA code uses the Macro Recorder to understand/clarify/remember how certain things are done. To use the Macro Recorder:




turn on the Macro Recorder (navigate to Tools | Macro | Record New Macro and follow the instructions)
perform manual things (like selecting cells, high-lighting cells, hiding rows, changing col widths, etc)
turn off the Macro Recorder (click on the blue square on the Macro Recorder window)
open the VB Editor (navigate to Tools | Macro | Visual Basic Editor)
click on the code module associated with your project (spreadsheet);NOTE: the Macro Recorder will have created a new code module called Macro1 or Macro2 or similar; poke around a bit to find the right one
examine the code generated during the manual operations in the right hand window
NOTE: the macro recorder normally generates verbose code with lots of statements that are probably not necessary. Edit out those that seem unnecessary and then test what is left (step 9)
Exit the VB Editor
To execute the (new) macro, navigate to Tools | Macro | Macros [or ALt+F8], high-light the target macro and click on Run

[markyc]

Thanks both for your comments, I need to actually delete the rows to reduce the size of the report, both in number of rows and phyiscal size.

I know how to use macros but just unsure of the code to use

[TonyJollans]

Hi markyc,

Welcome to VBAX, and sorry for the rather curt reply last night.

If you select column AN and then Select Data > Filter > Autofilter from the menu, then click on the dropdown at the top of column AN and select Custom...

In the dialog, select "does not equal" from the (first) dropdown on the left and "Aviation" from the first dropdown on the right, leave "And" selected, and then select "does not equal" from the (second) dropdown on th eleft and "Marine" from the second dropdown on the right, and press OK.

Now press Ctrl+A to select the visible rows and press Ctrl+- to delete the (selected) rows.

If you record that as per MWE's instructions you should have your code, which you can then tweak

[markyc]

Tony

Thanks for your reply,

I have recorded macro and amended slightly now does what I want.

Thanks