I use a named dynamic named range in my workbook for a daily report. After I copy into a spreadsheet a bunch of data, I manipulate some of the data. Some of the rows get deleted that I don't need. The odd thing is after I delete all of the unnecessary rows and create the named dynamic range, one blank row keeps appearing in the range. I then try to delete the blank row but when I view the range in the edit mode, I still see that the blank row is included.

Here is the formula that I am using:

=OFFSET('Daily Summary'!$A$2,0,0,COUNTA('Daily Summary'!$A:$A),COUNTA('Daily Summary'!$1:$1))

Can I add another layer of code to ignore a blank row?

Thanks.

Perhaps your method using COUNTA is off. I'd need to see the data and compare that to the dynamic named range address results to be sure.

Post up the workbook and let's take a look.

Perhaps your method using COUNTA is off. I'd need to see the data and compare that to the dynamic named range address results to be sure.

Post up the workbook and let's take a look. I use many dynamic named range techniques, I'm not sure I've ever used the one you've presented.

Quote:

---

Originally Posted by JBeaucaire

Perhaps your method using COUNTA is off. I'd need to see the data and compare that to the dynamic named range address results to be sure.

Post up the workbook and let's take a look. I use many dynamic named range techniques, I'm not sure I've ever used the one you've presented.

---

Jerry - here's a modified copy of the workbook... just one sheet in this example.

Incidentally, AMHD won't allow .xlsx docs to be attached so I had to save this as Excel 2003 with some loss of fidelity.

Thanks.

I'll take a look. If I'm not mistaken you can attach any file type by zipping it.

Well, I hate to say is seems obvious, but you do know what COUNTA() does, don't you?

COUNTA($A:$A) will give you a count of every cell in column A that has a value in it, including row 1 where your titles are. Since you obviously want the OFFSET() to skip row 1, you will need to subtract 1 from the count of column A.

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))