PivotTable with No Source Data

Hi All,

I have a workbook that has data on one sheet that is used to feed a
PivotTable on another sheet. I used a dynamic named range to allow the
PivotTable range to grow/shrink without including blank rows. I also
have macros coded to automatically refresh the PivotTable every time
the sheet that it's on is activated.

This setup works beautifully until all of the source data is deleted
(i.e. the only thing left on the sheet with the data is the row with
the column headings). If all of the source data is deleted, I receive
the following message when the PivotTable sheet is activated:

Run-time error '1004': This command requires at least two rows of
source data. You cannot use the command on a selection in only one row.
...."

I understand what the message means (a PivotTable requires more than
one row of data) but can anyone recommend an elegant way to handle this
situation? As the spreadsheet won't be used by me, I'd like the
solution to allow users to enter and remove data freely.

Any assistance would be greatly appreciated.

Thanks!
Kris

Hi

However you are defining your dynamic range, make the row count element
the maximum of 1 and rowcount.
something like
=OFFSET($A$1,0,0,MAX(2,COUNTA($A:$A)),12)
You have to have a header in row 1, hence COUNTA is bound to return 1,
so just force it to be 2 if there is just the single header line.

--
Regards

Roger Govier


<[email protected]> wrote in message
news:[email protected]...
> Hi All,
>
> I have a workbook that has data on one sheet that is used to feed a
> PivotTable on another sheet. I used a dynamic named range to allow the
> PivotTable range to grow/shrink without including blank rows. I also
> have macros coded to automatically refresh the PivotTable every time
> the sheet that it's on is activated.
>
> This setup works beautifully until all of the source data is deleted
> (i.e. the only thing left on the sheet with the data is the row with
> the column headings). If all of the source data is deleted, I receive
> the following message when the PivotTable sheet is activated:
>
> Run-time error '1004': This command requires at least two rows of
> source data. You cannot use the command on a selection in only one
> row.
> ..."
>
> I understand what the message means (a PivotTable requires more than
> one row of data) but can anyone recommend an elegant way to handle
> this
> situation? As the spreadsheet won't be used by me, I'd like the
> solution to allow users to enter and remove data freely.
>
> Any assistance would be greatly appreciated.
>
> Thanks!
> Kris
>

Thank you very much Roger ... that worked like a charm!

Oooops, spoke too soon. The solution worked in the sense that it
produced a blank PivotTable without generating an error but it caused a
problem with a date column in the PivotTable that's being grouped.

The PivotTable contains a date column that's grouped by Year and Month.
Refreshing with a blank data source destroyed this grouping. My guess
is that this is because you cannot grouping a column with blank rows.

Any ideas?

Thanks,
Kris

Hi Kris

Since you say you are running macros, could you not just ensure that
there is a single "row" of data by just writing a date to cell A2 of
the data sheet e.g. A2=TODAY()
Then, the count will still include just one "blank" row of data as well
as the headers, but the grouping will work because there will be a valid
date in row 1. It will not affect any future PT reports as all other
values in that row would be null.
--
Regards

Roger Govier


<[email protected]> wrote in message
news:[email protected]...
> Oooops, spoke too soon. The solution worked in the sense that it
> produced a blank PivotTable without generating an error but it caused
> a
> problem with a date column in the PivotTable that's being grouped.
>
> The PivotTable contains a date column that's grouped by Year and
> Month.
> Refreshing with a blank data source destroyed this grouping. My guess
> is that this is because you cannot grouping a column with blank rows.
>
> Any ideas?
>
> Thanks,
> Kris
>

Thanks again for the responses Roger - your suggestions were a big
help. The way I ended up solving the problem was to create two rows in
the source data that contained only the year and month and I used these
two columns in my pivot table rather than the original date column that
I was using. This approach allowed the PivotTable to automatically
group by year and month without having to define a custom grouping.

Sincerely,
Kris