Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro filters on different length worksheets
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro filters on different length worksheets
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. -- Regards, Tom Ogilvy "davegb" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro filters on different length worksheets
Tom, thanks for your reply. Been working on it, got it right finally.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Lists and Filters between Worksheets in Excel | New Users to Excel | |||
ADVANCED FILTERS WITH MACRO | Excel Discussion (Misc queries) | |||
Printing various length worksheets | Excel Discussion (Misc queries) | |||
Autmatically remove filters from all worksheets | Excel Programming | |||
Filters on Protected Worksheets | Excel Programming |