Macro: filtering out min AND max value from a column

Hello everyone, I'm Steve, new here. Came for excel guru expertise. Have a question about creating a macro that will filter out 2 (changing) values on a filter.

I use a report that comes to me weekly with multiple tabs (always named the same) and I spend a lot of time formatting the report the same way each week to get the information out of it I need. Naturally I decided to create a macro to speed up the process and do a lot of it for me. Problem is, on these multiple tabs, the values I am looking at each week and filtering out are not the same week to week, or tab to tab.

As it stands now, I apply a filter to row 1 (column headings), and then filter out the highest and the lowest values in a certain column. There is always a 1, followed by 2, 3, etc, all the way up into the hundreds or thousands. I always filter out 1 and whatever the highest value is. Since there is not a way to select min AND max, my current workaround is to use Number Filters and use the "between" function specifying greater than or equal to 2, and less than or equal to 100 since most of the time the value I am filtering out is not less than 100. But this is not foolproof and could potentially mess up my report should a less than 99 come back, for example. The other workaround I was looking into is conditional format that would use some sort of formula to color coordinate based off the determined max value on each tab, whatever it happens to be, and then filter out by color.

I am not sure if this would be best route, or if my current workaround is the best solution? I haven't been able to find anything else that will work and am not necessarily an expert on all things excel to know the easiest way to get it done. That is why I am here asking for guidance. I was told there may be a way to insert an additional column with a combination of if formulas, but haven't gone that far yet.



And then my follow-up question is if it is possible to build into the macro a way to copy and paste the contents of each tab into 1 tab at the end. I want all info (all formatted the same, same columns, same headings) from each tab copied over to a compiled list tab at the end, but I am not sure if this is possible. If it is, my second question is it possible to copy a certain format at a time? For example, if each tab contains red and green text/font, can I copy all red text to 1 tab, and green to a separate. I was trying to play with this today and copy all (from tab 1) and paste into the compiled view tab at the end, but was not able to figure out going back to tab 2, and pasting the contents under the already pasted tab 1 contents without overwriting it. I was attempting to do something like CTRL down to find the bottom value and then paste under it, but I may have messed it up. Not sure if there is some paste append option that I am missing.


Sorry for the long read, but these are the 2 snags I am hitting in creating a macro that would save me a lot of time. I appreciate any assistance. Thank you for your time.

awesome, thank you. I will try that tomorrow at work. that is very helpful.

what i had been doing was applying the filter to the columns, filtering out the 1 and max, selecting all and applying red font to those remaining, then unfiltering the sheet. if there is a way to apply the color to the desired rows (all but 1 and max), that could accomplish the same thing i am trying to do. it would actually be the rows who have an N/A in a separate column, and then the value of all except 1 and the max value. so i guess that could be something like an if and, but would require formulas somewhere.

let me ask one thing after glancing at that macro code....does this change anything if the values in said column appear more than once. for example....i do have multiple 1s as well as multiple of whatever the max value is. to shorten the list...i could appear as so...

1
1
1
2
2
3
3
3
4
4
4
4
4
4
4
5000
5000
5000
5000

kind of basic for my report, but gives an idea of what i am looking at. i would want to filter out 1 and 5000.

thank you for the help alpha. i did not bring a file home with me so i could not post an actual file. i will tonight if i have any trouble implementing these codes into the macro i am working on. i know it would be much easier for you to see the actual document. again, thanks for your help so far.