Anchor a Filter Switch?

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

I have what I call a collapsing spread sheet ... The
Template is large enough to hold ... say 1000 records, but
we may only use 300 ... then I run a recorded Macro that
collapses the sheet to the 300 rows used (Excel 2000).

To do this ... I insert a Filter Switch in a cell & then
have the Macro Filter Non-Blank records. This has always
worked great ... However, now for the 1st page of data I
want to keep a full page of rows regardless of Blank or
Non-Blank cells & then collapse subsequent rows only. To
do this I put the Filter Switch in Cell A43 ... This also
worked well until someone cleared the data from the Cell
containing the Filter Switch ... At this point the Filter
disappeared & the Macro Failed ...

I can "Lock" cell (A43), but this will prevent me from
entering a value to it ... Ideas Please ...

Thanks ... Kha
 
Can you put the criteria range on another sheet?

Range("Sheet1!_FilterDatabase").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Sheets("Sheet2").Range("A1:A2"), Unique:=False
 
Jim ...

You gave me an idea ... Instead keeping the Filter Switch
in cell A43 ... Can I include an instruction in my
recorded Macro to insert the Switch to cell A43 (or cell
of my choosing) when the Macro runs ... or ... is the
code you provided in your 1st response what I might need
to insert to achieve this ... Thanks for supporting this
board ... Kha
 
When you say "Filter Switch", do you mean that you apply an autofilter from
that
particular cell/row or what do you mean?

Sub testme()
With ActiveSheet
.AutoFilterMode = False
.Range("A43:A350").AutoFilter
End With
End Sub

will put the down arrow in A43
 
Peo ...

Yes ... In cell A43 (or cell of my choosing) I want to
put an AutoFilter arrow ... The purpose ... When Macro
selects AutoFilter NonBlank cells ... this will collapse
spread sheet to show only those rows containing data ...
However, for presentation purposes the 1st page will show
all rows of "format" should sheet contain less than 43
rows of data.

Thanks ... I will try your Macro instruction ... Kha
 
Back
Top