Excel 2002 Filter

[Chris waller]

A colleague who is using Excel 2002 is trying to filter some data. The
problem is that there are two columns within the range that he does not want
the filter arrows to appear on, for fear that if someone else got hold of it
they make a mess of it. I think that if you filter the data the range has to
be a continuous area and cannot be separated by blank columns. Can someone
confirm that this is correct or not? TIA

 

[Dave Peterson]

It's true that the filtered range has to be a contiguous range, but you can hide
the arrows using code (or maybe just hide the columns (or move the to the far
right or far left of the range to be filtered).

If you want to try the code stuff, visit Debra Dalgleish's site:
http://contextures.com/xlautofilter03.html#Hide

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[p45cal]

It's true that the filtered range has to be a contiguous range, but yo
can hide
the arrows using code (or maybe just hide the columns (or move the t
the far
right or far left of the range to be filtered).

If you want to try the code stuff, visit Debra Dalgleish's site:
'Excel Filters -- AutoFilter Programming
(http://contextures.com/xlautofilter03.html#Hide)

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
'Excel VBA -- Adding Code to a Workbook
(http://www.contextures.com/xlvba01.html)

David McRitchie has an intro to macros:
'Getting Started with Macros and User Defined Functions
(http://www.mvps.org/dmcritchie/excel/getstarted.htm)

Ron de Bruin's intro to macros:
'Where do I paste the code that I want to use in my workbook
(http://www.rondebruin.nl/code.htm)

(General, Regular and Standard modules all describe the same thing.)

I'd point out that in Excel 200*3*, a range which is not contiguous an
contains blank rows and/or columns can be filtered if you select th
whole range before applying the Autofilter.

In code, you can hide whichever dropdown arrows yo
like Range("F15:N26").AutoFilter 3, , , , Fals
'hide dropdown for 3rd column
Range("F15:N26").AutoFilter 5, , , , False 'hide dropdown for 5t
column
and of course, if the columns you don't want to sho
dropdown arrows for are at the extreme left or right of the range
simply don't include them when selecting the range to autofilter

 

[Dave Peterson]

The range to be filtered has to be contiguous in any version of excel.

The data within the cells in that range can be as empty as you want (empty rows
and columns within that contiguous range are fine).

(Just a clarification of what that contiguous range means.)

 

[p45cal]

It's true that the filtered range has to be a contiguous range, but yo
can hide
the arrows using code (or maybe just hide the columns (or move the t
the far
right or far left of the range to be filtered).

If you want to try the code stuff, visit Debra Dalgleish's site:
'Excel Filters -- AutoFilter Programming
(http://contextures.com/xlautofilter03.html#Hide)

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
'Excel VBA -- Adding Code to a Workbook
(http://www.contextures.com/xlvba01.html)

David McRitchie has an intro to macros:
'Getting Started with Macros and User Defined Functions
(http://www.mvps.org/dmcritchie/excel/getstarted.htm)

Ron de Bruin's intro to macros:
'Where do I paste the code that I want to use in my workbook
(http://www.rondebruin.nl/code.htm)

(General, Regular and Standard modules all describe the same thing.)

The range to be filtered has to be contiguous in any version of excel.

The data within the cells in that range can be as empty as you wan
(empty rows
and columns within that contiguous range are fine).

(Just a clarification of what that contiguous range means.)

(http://www.thecodecage.com/forumz/showthread.php?t=128026)

Yes, quite right; I was confusing the perceived need for 'continuous
area' in the first post of this thread, for which I understood to mean
'continuous data' with your 'contiguous' referring to a range.