You can create a range that automatically adjusts the number of rows. If you select Insert | Name | Define, give the range a name and then set Refers To as the following:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),29)
There are a couple of things to note with this method:
  1. It will add quite a load to your spreadsheet if you have lots of this type of range name, so use it judiciously.
  2. You won't see the range name listed in the Names drop-down list (to the left of the address bar) but you can still select it via code without any trouble.
  3. The COUNTA (which is used to count the number of rows) needs to be a row that is always populated in your data.

Hope this gets you started. Please post your code if you want some further help with this so we can see where you're at!