I've got over 16 000 line entries (stock). Out of the 16 000 I only want to filter 320 of the multiple stock codes in the "ITEM_CODE" column. Instead of selecting one at a time going through that huge list using the drop down filter, I want to select all at once (multiple stock codes). They do run in sequence i.e. row 620 to row 940. I only want to select that specific codes as I need to run a formula for those ones only. PS. I have never used or studied VBA to help me out so I have to do it with formulas etc. if possible.

  • Could you share some sample data with us along with the field name on which you want to filter records, will help us to fix the issue. – Rajesh S Jun 30 '18 at 5:56

You could say

  if(and(row()<x,row()>y),formula, 0)

If you want to be able to sort the list (I will assume it's an Excel table, not a standard range), start by creating a list of the items codes you want to calculate the formula on...

Then add a formula like

   If(iserror(match(@[item_code],{created list},0)),0, formula)

If it's just filtering the list you are after, and it is all sorted, you could select the lot using Ctrl + g; a640:a480.

Then copy those cells to a new location, and use a slight variation on the second formula.

| improve this answer | |

I usually solve these type of problems by adding another column in the table.

In this column I add an if that will be true on the rows that I need.
Example if the number you are looking for is between 100 and 200 then use:


Change "A" to the column you want to filter on. I assume your table starts at row 2.
And just fill down the column. Then you can filter on this new column.

| improve this answer | |

Probably the simplest way to filter for a contiguous range of values is to select them, change their fill colour and then filter by colour.

A quick way to do this would be, for your example:

  • Select the ITEM_CODE cell of row 620
  • Shift+Page Dn multiple times and then Shift+Up and/or Shift+Down until the range down to row 940 is selected
  • Change the fill colour of the selection
  • Filter for that colour

Worksheet Screenshot

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.