Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range, hidden row, auto-filter & macro buttons
My named range starts w/Row 14, which is hidden. A macro button inserts a
new row below 14 so users may enter a new record. There are several reasons Im doing it this way. 1) Inserting a new row between 14 & 15 ensures my named range is never altered, 2) Row 14 has conditional formatting that is copied onto Row 15 - keeping fields yellow until data is entered, and 3) the new record is always entered directly below directly below column titles. Aside from entering records I want users to be able to easily navigate through the large list or create custom narrowed down lists, so another macro button turns on AutoFilter for the named range. It works great until someone clicks show "All", which unhides Row 14 and makes it an official part of the list. I've tried eliminate the hidden row and instead have my macro insert a new row at the very beginning of my named range, but the new row is always excluded from my named range. Any suggestions or tips? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range, hidden row, auto-filter & macro buttons
Not sure whether the below directly answers your query. You can create a
dynamic named range.....say in colA Goto InsertNameDefine Name: MynamedRange Refers to: =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A)) -- Jacob "Munchkin" wrote: My named range starts w/Row 14, which is hidden. A macro button inserts a new row below 14 so users may enter a new record. There are several reasons Im doing it this way. 1) Inserting a new row between 14 & 15 ensures my named range is never altered, 2) Row 14 has conditional formatting that is copied onto Row 15 - keeping fields yellow until data is entered, and 3) the new record is always entered directly below directly below column titles. Aside from entering records I want users to be able to easily navigate through the large list or create custom narrowed down lists, so another macro button turns on AutoFilter for the named range. It works great until someone clicks show "All", which unhides Row 14 and makes it an official part of the list. I've tried eliminate the hidden row and instead have my macro insert a new row at the very beginning of my named range, but the new row is always excluded from my named range. Any suggestions or tips? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto filter hidden cells | Excel Discussion (Misc queries) | |||
Changing the color of the data auto filter buttons | Excel Worksheet Functions | |||
Column hidden and Auto filter | Excel Worksheet Functions | |||
Command Buttons and Auto-Filter | Excel Programming | |||
Hidden Rows with Auto-Filter | Excel Worksheet Functions |