End and Sort methods adding blanks
 

I have been working on some macro code that uses the End(xlDown) method. I
find that occasionally this method locates a cell two rows below the actual
end of the data. I call the End method on a range containing just the cell
at the top of the column. The blank cells that it indicates are filled are
actually empty; they don't contain spaces or anything like that. I've added
fudge code to back up if End(xlDown) skidded past the real stop point, but
I'm wondering why this happens.

I also find that when I use the Sort method from a range containing an
entire column and using ascending order, it sometimes puts 2 or 4 blank cells
at the top of the resulting column. Again, these are empty cells, not cells
containing spaces.

In both these cases, the cells in the column contain text information
consisting of several alphanumerics, a hyphen, and several digits. Example:
"sys-1163".
--
In theory, there is no difference between theory and practice; in practice,
there is.

End and Sort methods adding blanks
 

I'm betting you had some formulas that evaluated to "" and converted to values.
If you did, you can see a single apostrophe in the formula bar of one of those
cells if you toggle this setting:

Tools|Options|Transition tab|check the transition navigation keys box
(uncheck after you're done checking.)

I like to clean up that detritus with this technique:

select the range (ctrl-a (twice in xl2003) will get all the cells)
edit|replace
what: (leave blank)
with: $$$$$ (some unique string)
replace all

followed by:
edit|replace
what: $$$$$ (that same unique string)
with: (leave blank)
replace all

If you need a macro, record one when you do it manually.

And after you clean up that kind of junk, you can reset the last used cell by
using techniques at Debra Dalgleish's site:
http://www.contextures.com/xlfaqApp.html#Unused


LabElf wrote:

I have been working on some macro code that uses the End(xlDown) method. I
find that occasionally this method locates a cell two rows below the actual
end of the data. I call the End method on a range containing just the cell
at the top of the column. The blank cells that it indicates are filled are
actually empty; they don't contain spaces or anything like that. I've added
fudge code to back up if End(xlDown) skidded past the real stop point, but
I'm wondering why this happens.

I also find that when I use the Sort method from a range containing an
entire column and using ascending order, it sometimes puts 2 or 4 blank cells
at the top of the resulting column. Again, these are empty cells, not cells
containing spaces.

In both these cases, the cells in the column contain text information
consisting of several alphanumerics, a hyphen, and several digits. Example:
"sys-1163".
--
In theory, there is no difference between theory and practice; in practice,
there is.

--

Dave Peterson

End and Sort methods adding blanks
 

Dave -
I followed your instructions and found that the ' did show up in the cells
that Excel was including in the used range and the sort. There is in fact a
lot of formula massaging on the worksheet in the macros before this symptoms
manifest. I'm going to implement your suggestions for cleaning up the
formula residue and resetting the used ranges. Thanks for the link to
Debra's site.
- Lab Elf
--
In theory, there is no difference between theory and practice; in practice,
there is.


"Dave Peterson" wrote:

I'm betting you had some formulas that evaluated to "" and converted to values.
If you did, you can see a single apostrophe in the formula bar of one of those
cells if you toggle this setting:

Tools|Options|Transition tab|check the transition navigation keys box
(uncheck after you're done checking.)

I like to clean up that detritus with this technique:

select the range (ctrl-a (twice in xl2003) will get all the cells)
edit|replace
what: (leave blank)
with: $$$$$ (some unique string)
replace all

followed by:
edit|replace
what: $$$$$ (that same unique string)
with: (leave blank)
replace all

If you need a macro, record one when you do it manually.

And after you clean up that kind of junk, you can reset the last used cell by
using techniques at Debra Dalgleish's site:
http://www.contextures.com/xlfaqApp.html#Unused


LabElf wrote:

I have been working on some macro code that uses the End(xlDown) method. I
find that occasionally this method locates a cell two rows below the actual
end of the data. I call the End method on a range containing just the cell
at the top of the column. The blank cells that it indicates are filled are
actually empty; they don't contain spaces or anything like that. I've added
fudge code to back up if End(xlDown) skidded past the real stop point, but
I'm wondering why this happens.

I also find that when I use the Sort method from a range containing an
entire column and using ascending order, it sometimes puts 2 or 4 blank cells
at the top of the resulting column. Again, these are empty cells, not cells
containing spaces.

In both these cases, the cells in the column contain text information
consisting of several alphanumerics, a hyphen, and several digits. Example:
"sys-1163".
--
In theory, there is no difference between theory and practice; in practice,
there is.

--

Dave Peterson