• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

excel template filter moves in created document created from template

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
stretchr
Asked:
stretchr
  • 3
  • 3
2 Solutions
 
Arno KosterCommented:
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
 
stretchrAuthor Commented:
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
 
stretchrAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Arno KosterCommented:
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
 
stretchrAuthor Commented:
Silly me, I didn't even notice that your original range started at G2. Thanks!
0
 
Arno KosterCommented:
no problem. you're welcome !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now