I use pivottables that grab data by weeks or months. In order to get the
pivottable to group by week/month, there can be no null values in the date
columns, or an error occurs.

The source data is an excel spreadsheet that updates daily with the new
information. Each day, we have to reset the source data of the pivottable to
include this new data in new rows. We can't tell the pivot table to grab
1-10,000 rows, or there will be blanks in the date columns, and the error
occurs.

Is there a way to dynamically tell the pivottable to grab the portion of the
spreadsheet where there is data?

See
http://www.contextures.com/xlPivot01.html