Dynamic range formula not counting completely

**TK5** · 07-08-2008, 09:35 AM

I am trying to make a dynamic range for a pivot table. I use the following formula to make the range dynamic. This has worked in the past but I can't get it to work this time. It only counts down to row 6776 I am not even sure where to start the troubleshooting.

Any suggestions?

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),13)

A picture is attached.

Thanks for your help.

**oldchippy** · 07-08-2008, 09:38 AM

Hi,

Couldn't see your picture, but may be this link will help?

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

**VBA Noob** · 07-08-2008, 09:39 AM

Can't see your picture. Only assume you have blanks in Col A (Won't work with blanks)

HTH

VBA Noob

**TK5** · 07-08-2008, 09:53 AM

I attached another picture.

Blanks - That is what I thought but I can't find any blanks. I maybe the cells are formatted wrong but I can't seem to find it.

I can't attach the worksheet but I could send a few rows/columns if that would help.

It usually isn't this hard.

-Todd

**VBA Noob** · 07-08-2008, 09:58 AM

Without seeing the file.

1). Is the Sheet name called Data.
2). In the pivot table wizard is your named range show in the area.
3). To check for blanks. Select Col A > Press F5 > special > Blanks

As suggested by Chippy I would re-read the link on Debra's site

VBA Noob

**TK5** · 07-08-2008, 10:11 AM

I found it. It had nothing to do with cell 6776. There were a bunch of blanks early on in the column A so the count was short, leaving the named range short.

I missed an easy one. Sometimes the little things are what really trips you up...

Thanks for your help guys.

Cheers,
Todd

Dynamic range formula not counting completely

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Dynamic range formula not counting completely

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions