Dynamic Range Problem 1

[mattygriff]

Hi.

I have set up a dynamic range in a worksheet but am getting a rather weird result - as can be seen in the attached screengrab, the range is including 5 empty cells below the last populated cell.

I have checked that the 5 cells are empty - clicking CTRL+down takes me to the last populated cell (row 39) and then to the end of the sheet (row 65536).

It's probably something simple that I'm missing but it's bugging the hell out of me!

Thanks in advance.

 

[xlbo]

what is the formula definition of your dynamic range and what is above row 7 in your spreadsheet? I'm guessing you have 5 rows above that which have some text / data in which are being counted in your COUNTA part of the range definition...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mattygriff]

It's OK - I've worked it out. There are 5 populated cells in Column A above my anchor cell of A8 and I need to deduct these from the COUNTA($A:$A) calculation.

I assumed that the COUNTA started from the anchor cell and worked down which is clearly not the case.

 

[mattygriff]

Sorry Geoff - your reply must have landed as I was typing my own.

Thanks anyway.

 

[xlbo]

lol - COUNTA($A:$A) is your clue!

If you don't want to subtract a hard coded number, you can always use:

Counta($A$7:$A$65536")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mattygriff]

That last tip is a good one - might just go for that solution.

Thanks again.

 

[xlbo]

no probs - only thing to consider is that you are still hard coding - just in your range ref rather than removing a value from the count

I generally try to have all my tables starting in A1 to get around this type of thing but its not always possible...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question