count blank cells in a dynamic range
 

I have a pivot table that shows a list of people in the row field and a list
of dates in the column field (across the top) and a piece of information in
the data field.

I need to be able to insert a formula in a column immedietly to the left of
the pivot table that will calculate the average number of blank cells between
each value (the part that I'm really having trouble with is getting the range
for the countblank formula to only start counting across when it gets to the
first value... meaning I don't want to count the blank cells before the first
date because they don't matter...)

I also need to be able to create a formula that will give me the date of the
first piece of information, so basically just have it look for the first
value for each person and have an absolute reference to display the date in
the column field of the pivot table.

I've looked online and tried a similar post but I can't seem to find
anything about setting dynamic ranges in a row (usually I just find info on
how to do it in a column with set range xlup etc code)

Thanks!

count blank cells in a dynamic range
 

set rng = cells(3,"IV").End(xltoLeft)

--
Regards,
Tom Ogilvy


"Still Learning" wrote:

I have a pivot table that shows a list of people in the row field and a list
of dates in the column field (across the top) and a piece of information in
the data field.

I need to be able to insert a formula in a column immedietly to the left of
the pivot table that will calculate the average number of blank cells between
each value (the part that I'm really having trouble with is getting the range
for the countblank formula to only start counting across when it gets to the
first value... meaning I don't want to count the blank cells before the first
date because they don't matter...)

I also need to be able to create a formula that will give me the date of the
first piece of information, so basically just have it look for the first
value for each person and have an absolute reference to display the date in
the column field of the pivot table.

I've looked online and tried a similar post but I can't seem to find
anything about setting dynamic ranges in a row (usually I just find info on
how to do it in a column with set range xlup etc code)

Thanks!