O
Otto Moehrbach
Excel 2003, WinXP
I think I need some help with this one.
I am doing everything with VBA.
I have some 18 pages of data.
I copy the same column, from row 6 to the last entry, in each sheet and
paste it to the bottom of a list in a Utility sheet.
There are many blank cells in this copied data.
I sort the final list in the Utility sheet.
This puts all the blank cells to the bottom of the list. Great.
Except for one cell.
This cell has a (=Len) of 0.
(=IsBlank) says it is not blank.
I reset the name of the list and use that list in a Data Validation cell.
Data Validation sees that one cell as blank and you know what that does.
Interesting fact: I can select that one cell in the Utility sheet and do
Edit - Clear - All and a subsequent Sort treats that cell as a true blank
cell and puts it at the bottom of the list.
I can write a looping code to go through all the original cells in the 18
sheets, check that Len = 0, and if it does, execute an Edit - Clear - All
(in VBA), and clean up that one bad cell. Until the user does whatever to
mess up another cell..
Or I can do the same thing to the list in Utility sheet within the code that
sets up the list. This would run whenever the user resets the list.
My question is this: What is happening with this one cell?
Thanks for your help. Otto
I think I need some help with this one.
I am doing everything with VBA.
I have some 18 pages of data.
I copy the same column, from row 6 to the last entry, in each sheet and
paste it to the bottom of a list in a Utility sheet.
There are many blank cells in this copied data.
I sort the final list in the Utility sheet.
This puts all the blank cells to the bottom of the list. Great.
Except for one cell.
This cell has a (=Len) of 0.
(=IsBlank) says it is not blank.
I reset the name of the list and use that list in a Data Validation cell.
Data Validation sees that one cell as blank and you know what that does.
Interesting fact: I can select that one cell in the Utility sheet and do
Edit - Clear - All and a subsequent Sort treats that cell as a true blank
cell and puts it at the bottom of the list.
I can write a looping code to go through all the original cells in the 18
sheets, check that Len = 0, and if it does, execute an Edit - Clear - All
(in VBA), and clean up that one bad cell. Until the user does whatever to
mess up another cell..
Or I can do the same thing to the list in Utility sheet within the code that
sets up the list. This would run whenever the user resets the list.
My question is this: What is happening with this one cell?
Thanks for your help. Otto