If you need to create pivot tables based on a dynamic named range that will expand as you add additional rows of data you. If just selecting complete columns blank values will show up in the pivot filters. Basically an easy formula can be used therefore:
=OFFSET(Sheet1!$A$1;0;0;COUNTA(Sheet1!$A:$A);4)
Further information for the offset formula can be found on the excelpivots.com blog.
