Macro filters on different length worksheets

[davegb]

I'm creating a series of spreadsheets in a workbook for my clients. I
was originally told the each worksheet would have the same number of
rows in it, 64, the number of counties in the state.

I created a test spreadsheet and sent it out to a sampling of my end
users. It contained buttons/macros that filtered the sheet for certain
subsets of counties that met special criteria. I created special
colomns (hidden) for the flags for some of those criteria. Others are
based on the numbers in the sheet itself. I used Advanced Filters and
put the criteria in cells off to the right. And I added a button to
remove the filters and show all the data. By experimentation, I found
the best way to do this in a protected worksheet was to apply a null
filter (blank criteria range) rather than a "ShowALLData" instruction.
Then I just recorded macros to do the filtering. And assigned the
buttons to the macros. Worked great!

Now that I'm receiving the actual data, being downloaded from SPSS, the
number of rows vary between worksheets, between 60 and 64. So the range
of cells that are to be filtered and un-filtered varies from sheet to
sheet. Now, when I apply the filters, I get different results depending
on which sheet I'm in and how many rows of data it has. Sometimes the
filter works, but leaves rows not displayed up at the top (hidden
behind frozen rows)until I scroll up to see them. Sometimes I get
totally wrong results, more fields filtered out than should be. And
it's also created problems removing the filters by using a null filter.
Sometimes it works, sometimes not.

I wanted to use the same set of macros to do all the sheets (when I'm
done, there will be about 13 sheets). Is there a way to set the range
to be filtered and unfiltered for each sheet, even in a protected
worksheet? I tried setting up each with a range name for the filtered
area, and that didn't work either.

I should also mention that there is a row of totals and percentages at
the bottom that I want to remain in sight whether the sheet is filtered
or not.

Thanks for the help.

 

[Tom Ogilvy]

If is fairly straight forward to examine the sheet and determine where the
data is located.

I suspect you need to alter your code to include such activities, then the
discovered range would be used in your filtering commands.

If this is a static activity, where you would walk through the 13 sheets and
do it one time, then using a named range should work.

Named ranges can also be set up do be dynamic in determining the range they
refer to.

There are a number of ways to attach these and associated problems. In most
cases the choice of an approach would be specfic to the conditions and your
general description doesn't point to any specific approach.

 

[davegb]

Tom, thanks for your reply. Been working on it, got it right finally.