Dynamic Data Range from External Text File, Filter

[Chad Richardson]

Several issues I'm hoping some smarty pants can help with

I am importing data from a text file that the user will refresh on open.
There are 5 columns to the right of the imported data used for calculated
fields.

There are several tabs that have pivot tables that use the dynamic imported
data on the first tab as its source.

Question 1:
How to have the pivot tables reference a range that will grow/shrink based
on the nbr of rows in the refreshed data AND include the added calculated
columns. I know you can use the Query name as the pivot table source, but it
will not include those 5 calculated columns. Or I can create a name range
that includes the 5 columns, but will not shrink/grow with the refreshed
data. I need to combine the 2 methods somehow....

Question 2:
How can I filter the text data so certain rows are not included in the pivot
table? (I am using average summations and I want to throw out rows that have
zeros for values as there are many and skew the averages incorrectly).

Thanks in advance!
Chad

 

[Debra Dalgleish]

You can use an offset from the query name to create a dynamic range, e.g.:

=OFFSET(QueryName,,,,COUNTA(Sheet1!$1:$1))

 

[Mike Fogleman]

I use the named range method. The PT source is the range name. Upon adding
new data to the source, I then re-size the named range.

Dim DRow As Long
Dim DRng As Range

DRow = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Set DRng = Worksheets("Data").Range("A1:G" & DRow)
ws.Names("Database").RefersTo = "=" & DRng.Address(1, 1, xlA1, True)

Hope this helps some
Mike F