Whatever I do I get the wrong result

[Arishy]

There is a weak area in VBA Excel that is driving me crazy.!

The use of End(xlDown)

Even John Walkenbach states it clearly in his wonderful book.
"The use of End method may not produce the desired result..."

There is only one place ...here... that I feel confident that will
provide the answer..(Yes, I believe there must be a solution).

To state this challenge as clear as possible :

I have a column say B The column HAS blank cells

Column A Column B:

1 Blank
2 1
3 1
4 1
5 Blank
6 Blank
7 2
8 2
9 2
10 Blank
11 3
12 3
13 3


I need to get the ROW numbers for start and finish of group "1" etc

If the activecell is on cells(2,2) the cell value "1"

Range(activecell,activecell.End(xlDown).select

I get the address of the last "3" cells(13,2)

the "correct answer" should be cells(4,2).

I am sure you will come up of a way around it, but how one using the
END method come up with the code that will give reliable answer. and
while we are at it WHY we get wrong answer in the first place.

 

[Norman Jones]

Hi Arishy,

Range(activecell,activecell.End(xlDown).select

Should read:

Range(ActiveCell, ActiveCell.End(xlDown)).Select

Note the second closing parenthesis.

Your experience is consistent with your 'blank' cells not being empty.
Perhaps these cells contain a formula which returns an empty string. If the
intervening cells are not empty, the your code will not produce the results
you expect, precisely as John Walkenbach explains.

Incidentally, if your intention is to select the last cell in the current
block, perhaps try:

ActiveCell.End(xlDown).Select

 

[Norman Jones]

Hi Arishy,

The suggested use of

ActiveCell.End(xlDown).Select

referred to your described scenario.

If the active cell were blank, or the last populated cell in the current
block of column cells, the above instruction would select the first
subsequent populated cell, or the last cell in the column if the active cell
represented the last populated cell in the column.

 

[Tom Ogilvy]

and for the special case, if you want the cells containing numbers (stored
as numbers) and they are constants not formulas

Dim rng as Range
set rng = Columns(2).Specialcells(xlconstants,xlNumbers)
for each ar in rng.areas
msgbox ar.address
Next

adjust to fit your scenario

 

[Arishy]

Hi Norman,

Sorry about the typo...I am sure you knew it was NOT the problem.
As for the the error, for me to prove your point I had to make the cell
really Blank. Then it worked. That applied to all my trials So, thank
you for pointing out the reason. Now for the solution; I was pretty
sure I never put any formula or "anything" in the blank cells to be
detected. But I do remember that the first time I used the code it
worked then it did not; so I presume the balnk cells got "corrupted"
for unknown reason. In the real world this will happen I am sure ..so
here is my question to you:

How do I avoid this from happenning Do I go before applying the macro,
and check that every blank cell is really blank ( catch 22)
There must be a better way.!!!!

 

[Arishy]

My very dear Tom

What a solution... WoW....You opened a new world for me by your simple
yet elegant proceedure.
I will build my solution based on your routine. Thank you again for
sharing your valuable knowledge and I do hope I pay back this wonderful
forum.