AutoFilter Header Row

  • Thread starter Thread starter bgetson
  • Start date Start date
B

bgetson

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?
 
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?
Click to expand...

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.
 
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.
Click to expand...

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?
 
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?
Click to expand...

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.
 
What I mentioned earlier would probably work as a worksheet fuction.
Something like:

=IF(SUBTOTAL(103,$A$1:$A & [current row - 1])=0,$A$1,$A1)

But, this would cause massive slowdowns with rows in excess of 10,000,
and I eventually want to run this process on 100,000+ rows. My idea is
to use VBA to calculate this subtotal at the end of my userform macro
on the filtered data. This would then populate the results into an
additional column inside of my table. This means that any calculations
I would do on the filtered data would act as they do now, because I'm
only referencing an additional cell per row, instead of an entire
array per row (as a worksheet function would do).

I think an alternative might be to cycle through all of my rows and
find the first that isn't hidden, but I haven't tried that yet.

This is the best answer I've come up with so far to solve my problem,
and the only drawback is that my inputs are still hidden when
everything is filtered (but that can easily be fixed by moving them
somewhere else).

BTW, a word to the wise: apparently hitting ESC inside of a textbox
online deletes all text typed, as I (unfortunately) just found out.

Thanks for all of the help.
 
Back
Top