I don't know what errors you're getting, but perhaps you could avoid
them by changing the range definition slightly:

=OFFSET(Sheet1!$C$1,0,0,MAX(1,COUNTA(Sheet1!$C:$C) ),1)

If the sheet is cleared, the range would be one blank cell.

John wrote:
By having Dynamic Range names set and Clearing Contents of all the Worksheet
i.e. A:IV does this effect the Offset feature that is coded within these
Dynamic Range Names? I'm getting errors when I clear ALL the worksheet that
the Dynamic Range name is in

I am using Debra Dalgleish example at
http://www.contextures.com/xlNames01.html#Dynamic

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html