Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 he 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.) Chris waller wrote: 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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Dave Peterson;462943 Wrote: 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: '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 he '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.) Chris waller wrote: 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 I'd point out that in Excel 200*3*, a range which is not contiguous and contains blank rows and/or columns can be filtered if you select the whole range before applying the Autofilter. In code, you can hide whichever dropdown arrows you like Range("F15:N26").AutoFilter 3, , , , False 'hide dropdown for 3rd column Range("F15:N26").AutoFilter 5, , , , False 'hide dropdown for 5th column and of course, if the columns you don't want to show dropdown arrows for are at the extreme left or right of the range, simply don't include them when selecting the range to autofilter. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128026 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 wrote: Dave Peterson;462943 Wrote: 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: '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 he '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.) Chris waller wrote: 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 I'd point out that in Excel 200*3*, a range which is not contiguous and contains blank rows and/or columns can be filtered if you select the whole range before applying the Autofilter. In code, you can hide whichever dropdown arrows you like Range("F15:N26").AutoFilter 3, , , , False 'hide dropdown for 3rd column Range("F15:N26").AutoFilter 5, , , , False 'hide dropdown for 5th column and of course, if the columns you don't want to show dropdown arrows for are at the extreme left or right of the range, simply don't include them when selecting the range to autofilter. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128026 -- Dave Peterson |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Dave Peterson;462943 Wrote: 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: '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 he '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.) Chris waller wrote: 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 Dave Peterson;463189 Wrote: 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 wrote: Dave Peterson;462943 Wrote: 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: 'Excel Filters -- AutoFilter Programming' ('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 he 'Excel VBA -- Adding Code to a Workbook' ('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' ('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' ('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.) Chris waller wrote: 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 I'd point out that in Excel 200*3*, a range which is not contiguous and contains blank rows and/or columns can be filtered if you select the whole range before applying the Autofilter. In code, you can hide whichever dropdown arrows you like Range("F15:N26").AutoFilter 3, , , , False 'hide dropdown for 3rd column Range("F15:N26").AutoFilter 5, , , , False 'hide dropdown for 5th column and of course, if the columns you don't want to show dropdown arrows for are at the extreme left or right of the range, simply don't include them when selecting the range to autofilter. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 'The Code Cage Forums - View Profile: p45cal' (http://www.thecodecage.com/forumz/member.php?userid=558) View this thread: 'Excel 2002 Filter - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=128026) -- Dave Peterson 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. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128026 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel 2002: Auto Filter Sub Total formula | Excel Discussion (Misc queries) | |||
excel 2002 data filter | New Users to Excel | |||
Excel 2002: How to use Auto Filter ? | Excel Discussion (Misc queries) | |||
Excel 2002/2003 Advanced Filter | Setting up and Configuration of Excel | |||
Excel 2002 - Is it possible to use Data,Filter,Autofilter as X-Axi | Charts and Charting in Excel |