[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel template filter moves in created document created from template

Posted on 2012-08-21
6
Medium Priority
?
401 Views
Last Modified: 2012-08-24
Hello everyone,

Thanks for helping to make life easier!

I have a problem that's been bugging me for a while now, not a critical one but annoying nevertheless. I have a template (.xlt) set up for generating plant schedules that we use on our landscape planting plans. The excel file is basically a categorized list of all the plants we typically use, but for each project we only use a small selection of those. A column at the far right of the schedule shows the total numbers of each plant.

There are two macros in the template, one to filter on the Totals column to hide all rows without anything in the Totals column, the other to show all rows again.

The macros are basically this:
Sub PlantList1()
'
' PlantList1 Macro
' Reduce Plant List
'

'
    Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlOr, _
        Criteria2:="=***"
End Sub
Sub TotalPlantList1()
'
' TotalPlantList1 Macro
' Show Total Plant List
'

'
    Selection.AutoFilter Field:=1
End Sub

Open in new window


This is all fine, and the first few rows of the template file look like this:
plantschedule2012-08-22-143944.pdf

As you can see, there is a filter in column G, which has a list range of $G$1:$G$870

Now, take a look at what happens when a new document is generated from the template:
plantscheduleB-2012-08-22-144132.pdf

The filter range has changed from the template, to this: $A$2:$G$867. The macros still work fine, but I would like the filtering to remain as it is in the template, because the filter arrows start to obscure the text in row 2, and because of the way we use the excel file in the final product, these arrows all still show. I would like to make some of the columns narrower but I can't because otherwise the text in those columns would not be able to be read properly.

So, can anyone tell me why this happens between the template and the new document? It's not specific to the version of Excel either, we have both 2003 and 2007 and the behaviour is the same.

Look forward to your replies.

Richard
0
Comment
Question by:stretchr
  • 3
  • 3
6 Comments
 
LVL 19

Accepted Solution

by:
Arno Koster earned 2000 total points
ID: 38319847
The PlantList1 macro works on 'selection'. This means that whatever columns are selected when you run the macro will be used for the autofilter.

try using this instead:
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("G2:G867").AutoFilter field:=1, Criteria1:=">0", Operator:=xlOr, Criteria2:="=***"

Open in new window


first statement disables any existing filters
second statement adds the filter to the G column only
0
 

Author Comment

by:stretchr
ID: 38320026
Ok thanks, looks like it might do the trick. I didn't write the macro in the first place and I don't have the time to work out what it does.

I'll give it a whirl tomorrow and post back here with the results.
0
 

Author Comment

by:stretchr
ID: 38323119
That works for keeping it to the one column. I noticed though that the filter arrow still moves from row 1 to row 2 the first time the macro is run. Is there any way of keeping it in  G1?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Assisted Solution

by:Arno Koster
Arno Koster earned 2000 total points
ID: 38324931
that can be solved by replacing

ActiveSheet.Range("G2:G867").AutoFilter field:=1, Criteria1:=">0", Operator:=xlOr, Criteria2:="=***"

Open in new window

by

ActiveSheet.Range("G1:G867").AutoFilter field:=1, Criteria1:=">0", Operator:=xlOr, Criteria2:="=***"

Open in new window


The arrow is placed in the top cell(s) of the range
0
 

Author Comment

by:stretchr
ID: 38327301
Silly me, I didn't even notice that your original range started at G2. Thanks!
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 38328784
no problem. you're welcome !
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question