I want to apply an AutoFilter to a Structured Table within my
worksheet. Row 1 is a header row, and row 2 contains a series of
inputs needed for calculations within the table. I only want to apply
the AutoFilter to row 3 and down (which contain dates), but my code
always filters out my input row because it doesn't contain a date. I'm
using:

ActiveSheet.Range("A3").AutoFilter field1:=1, ..........

Any suggestions?

On Aug 17, 11:06 am, bgetson wrote:
I want to apply an AutoFilter to a Structured Table within my
worksheet. Row 1 is a header row, and row 2 contains a series of
inputs needed for calculations within the table. I only want to apply
the AutoFilter to row 3 and down (which contain dates), but my code
always filters out my input row because it doesn't contain a date. I'm
using:

ActiveSheet.Range("A3").AutoFilter field1:=1, ..........

Any suggestions?

Move your inputs in to a row above your table. If your header row is
row 1 in your spreadsheet, just insert a row above it.

On Aug 17, 2:06 pm, Ferris wrote:
Move your inputs in to a row above your table. If your header row is
row 1 in your spreadsheet, just insert a row above it.

The problem is that my formulas are recursive. Because I have several
rows of data (anywhere from 11,000 to 100,000), this is the easiest
and quickest way for me to do several calculations at once. This means
that my inputs have to be directly above the calculations. They are
basically initial conditions for 2-3 of 20 columns.

Maybe it would make more sense if I phrase my problem a different way.

I have a table full of data that is sorted in ascending order based on
time. There is a row of initial conditions at the top of the table so
that my recursive formulas can reference the previous time interval
and evalulate change.

I am creating a macro that will restrict my data based on a time
interval that I create (through a user form), with the ultimate goal
of updating the first non-filtered row so that it references the
initial conditions (instead of the hidden row above it).

What would be the best way to go about this?

On Aug 17, 1:24 pm, bgetson wrote:
Maybe it would make more sense if I phrase my problem a different way.

I have a table full of data that is sorted in ascending order based on
time. There is a row of initial conditions at the top of the table so
that my recursive formulas can reference the previous time interval
and evalulate change.

I am creating a macro that will restrict my data based on a time
interval that I create (through a user form), with the ultimate goal
of updating the first non-filtered row so that it references the
initial conditions (instead of the hidden row above it).

What would be the best way to go about this?

I would still move the initial conditions above the table - just so it
never gets hidden by your filter, and because it really isn't part of
your data. It sounds like you may need to do your calculations in VBA.
I'm not sure how you'd manage skipping over hidden rows in your data
with a formula in a sheet. In VBA you could loop through your data
and on the first visible row use the initial values and on subsequent
visible rows use the previous visible rows values. If you've found a
way to do a similar process in an Excel formula I'd love to see it,
always looking to add something to my toolkit.

There might be some way of using a subtotal to count the visible rows
and reference the inital conditions if the count returns 0, but I
think that may be too much trouble than it's worth. I was afraid of
the VBA alternative because of the work it would create in undoing the
changes each time I filter.

Thanks for your input.