Use End Down Arrow to go to next non-empty cell - not working

[teachman]

Hello,

This challenge has me very frustrated. Some set up before the question. I have values in A1 to A1500, some of which are duplicates and data in cells C1:C1500. Column B contains no data. I sort column A and C to get the duplicates in column A grouped together. The first set of duplicates is in A1:A10 and the second set is A11:A25 and so on ending up with the last value of the last group in cell A1500. There is no pattern to how many groups of duplicates there are in column A.

To make the table easier to read, I blank out the duplicates in column A. To do this, I put a formula in B1 (that gets copied down to B1500) that looks through column A and 'virtually' blanks out duplicate entries leaving the unique value in the last row of the group of duplicates. The formula ignores duplicates and leaves the unique value for the A1:A10 group in cell B10 and the unique value for the A11:A25 is in cell B25. So on and so forth finishing up in cell B1500.

I would really like the unique cell in A1. Meaning I want the unique value of the group at the top of the group, instead of the bottom. I figure I can write an easy VBA script to do this. In essence, I want to move the value in B10 to B1 and the value in B25 to B11. To start this process, I copy column B (in which every cell has a formula, well the cells in B1:B1500 do) and then moving to column D I paste just the values in column D. To get a head start on the vba script that moves the group's unique cell from the last row of the group to the first row of the group I start the macro recorder with the cursor in cell D1, which is empty (at least I thought it was empty since a just pasted the result of the formula in column B into column D) I click the End key and then click the Down Arrow key, which should stop the cursor in cell D10.

This is where the problem comes in. Instead of the cursor moving from D1 to D10, it moves to D1500. Remember, the last unique value for the last group of duplicates in column A is processed with the formula in B1500 that results in the unique value of the last group of duplicates in B1500. Which, is then copied and pasted (just the value of the formula) into D1500. So, the last cell with a value is in D1500.

The Question: Why doesn't the cursor stop in the first non-blank cell in column D, which should be D10? I checked the blanked out cells that were pasted into column D and they have a length of 0. Which, to me, means there is nothing in the cell. But, the action to select the next non-empty cell in column D results in the cursor ending up in D1500 instead of D10.

Thanks for your help.

George Teachman

 

[George J]

I think this is one of the main differences between the old XP version of Excel that i'm used to and the newer versions.
If you copy the product of a formula to another cell then the value of the new cell is modified.
This means the cell is "" or blank, which means the cell is not empty.

Try using the =isblank(D10) formula against a blank cell and then test against one that has not had anything in it.

The way I fix this looks messy, but works.
CTRL + H to bring up find and replace
leave the 'find what' field blank
replace with: blahblahblah
do replace all

then find blahblahblah
and replace with an empty field (I usually press backspace and delete a few times to make sure)
replace all

If you leave your isblank formulas up, one will start as flase for the blank and true for the empty cell
Afterwards, both will state true.