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.

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.