Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing blanks / sorting - common methods not applicable | Excel Discussion (Misc queries) | |||
How do I sort a list that contains blanks that I want to keep? | Excel Discussion (Misc queries) | |||
Sort a row of strings, some containing blanks | Excel Programming | |||
Adding Chart - Methods Fail | Excel Programming | |||
3 possible methods for adding value to a combobox | Excel Programming |