Hi NG



I have an odd problem.



Sometimes I get a spreadsheet with records, e.g. 50000 pcs.

Column 2 seems empty, so I mark cell B1 and use key: End - arrow down.

Normally cell cursor stops at the first cell with info, but sometimes the
cell cursor goes to sheet button, so I think the column is empty and then
delete the column and thereby customerinformations.

Any suggestion to how I can avoid this problem?


--
Best Regards
Joergen Bondesen

Joergen

How are you deleting the column? If you do as I often do and hit
control space to highlight the column, alt edit delete, you may
inadvertantly delete additional data if you have any cells in column B
merged with cells in A or C since the control space will select any
columns that are merged with column B.

Where is your data that is deleted before it gets deleted? it seems
like deleting an empty column should not cause any loss of data; and
if end-down arrow takes you all the way to the bottom, the column is
either empty or full, and if it is full of data you would probably
know that.

Ken


On Nov 11, 2:45*pm, "Joergen Bondesen" wrote:
Hi NG

I have an odd problem.

Sometimes I get a spreadsheet with records, e.g. 50000 pcs.

Column 2 seems empty, so I mark cell B1 and use key: End - arrow down.

Normally cell cursor stops at the first cell with info, but sometimes the
cell cursor goes to sheet button, so I think the column is empty and then
delete the column and thereby customerinformations.

Any suggestion to how I can avoid this problem?

--
Best Regards
Joergen Bondesen

Hi Ken

Excuse my insufficient explanation.

I have 2 Columns A and B with 50000 Records.
Column A and B contains text (no formula).
In Column A contain all cells information.
In column B contain cell B2500 = test.

I select cell B1 and use key: End - arrow down.

Normally cell cursor stops at the first cell with info (B2500),
but sometimes the cell cursor goes to sheet button,
so I think the column is empty and then delete the column
and thereby customerinformations, in this case B2500 = test.

Is there a way to make the curser able to stop at B2500?

--
Best regards
Joergen Bondesen


"Ken" skrev i en meddelelse
...
Joergen

How are you deleting the column? If you do as I often do and hit
control space to highlight the column, alt edit delete, you may
inadvertantly delete additional data if you have any cells in column B
merged with cells in A or C since the control space will select any
columns that are merged with column B.

Where is your data that is deleted before it gets deleted? it seems
like deleting an empty column should not cause any loss of data; and
if end-down arrow takes you all the way to the bottom, the column is
either empty or full, and if it is full of data you would probably
know that.

Ken


On Nov 11, 2:45 pm, "Joergen Bondesen" wrote:
Hi NG

I have an odd problem.

Sometimes I get a spreadsheet with records, e.g. 50000 pcs.

Column 2 seems empty, so I mark cell B1 and use key: End - arrow down.

Normally cell cursor stops at the first cell with info, but sometimes the
cell cursor goes to sheet button, so I think the column is empty and then
delete the column and thereby customerinformations.

Any suggestion to how I can avoid this problem?

--
Best Regards
Joergen Bondesen

Hi Ken

Below macro makes it possible for me to use 'End - key down' with success.

The remaining question is, why this problem.



Option Explicit


'----------------------------------------------------------
' Procedure : AvoidNoneSelect
' Date : 20081112
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : To avoid none select first cell with info.
' Note : Only sometimes in customer excelfiles.
'----------------------------------------------------------
'
Sub AvoidNoneSelect()
Dim rrange As Range
Set rrange = Selection

Dim cell As Range
For Each cell In rrange
If Trim(cell.Value) = vbNullString Then
cell.Value = "'"
cell.Value = vbNullString
End If
Next cell
Set rrange = Nothing
End Sub

--
Best Regards
Joergen Bondesen


"Joergen Bondesen" skrev i en meddelelse
...
Hi Ken

Excuse my insufficient explanation.

I have 2 Columns A and B with 50000 Records.
Column A and B contains text (no formula).
In Column A contain all cells information.
In column B contain cell B2500 = test.

I select cell B1 and use key: End - arrow down.

Normally cell cursor stops at the first cell with info (B2500),
but sometimes the cell cursor goes to sheet button,
so I think the column is empty and then delete the column
and thereby customerinformations, in this case B2500 = test.

Is there a way to make the curser able to stop at B2500?

--
Best regards
Joergen Bondesen


"Ken" skrev i en meddelelse
...
Joergen

How are you deleting the column? If you do as I often do and hit
control space to highlight the column, alt edit delete, you may
inadvertantly delete additional data if you have any cells in column B
merged with cells in A or C since the control space will select any
columns that are merged with column B.

Where is your data that is deleted before it gets deleted? it seems
like deleting an empty column should not cause any loss of data; and
if end-down arrow takes you all the way to the bottom, the column is
either empty or full, and if it is full of data you would probably
know that.

Ken


On Nov 11, 2:45 pm, "Joergen Bondesen" wrote:
Hi NG

I have an odd problem.

Sometimes I get a spreadsheet with records, e.g. 50000 pcs.

Column 2 seems empty, so I mark cell B1 and use key: End - arrow down.

Normally cell cursor stops at the first cell with info, but sometimes the
cell cursor goes to sheet button, so I think the column is empty and then
delete the column and thereby customerinformations.

Any suggestion to how I can avoid this problem?

--
Best Regards
Joergen Bondesen

Joergen

It seems like what appears to be empty is not really empty. The trim
function will remove leading and trailing spaces, so your macro will
replace the cell value with vbNullString (make it really empty) if, in
fact, all that was there was a space (or multiple spaces). I believe
that cell.value="'" is extraneous.

If you edit a seemingly empty cell (before running your macro) you
will probably see that it contains a space. After the macro runs what
looked like it was empty is really empty, so the end-arrow
functionality then performs as you expect.

Good luck.

Ken
Norfolk, Va



On Nov 12, 3:38*pm, "Joergen Bondesen" wrote:
Hi Ken

Below macro makes it possible for me to use 'End - key down' with success..

The remaining question is, why this problem.

Option Explicit

'----------------------------------------------------------
' Procedure * : AvoidNoneSelect
' Date * * * *: 20081112
' Author * * *: Joergen Bondesen
' Modifyed by :
' Purpose * * : To avoid none select first cell with info.
' Note * * * *: Only sometimes in customer excelfiles.
'----------------------------------------------------------
'
Sub AvoidNoneSelect()
* Dim rrange As Range
* Set rrange = Selection

* Dim cell As Range
* For Each cell In rrange
* * If Trim(cell.Value) = vbNullString Then
* * * cell.Value = "'"
* * * cell.Value = vbNullString
* * End If
* Next cell
* Set rrange = Nothing
End Sub

--
Best Regards
Joergen Bondesen

"Joergen Bondesen" skrev i en bl...



Hi Ken

Excuse my insufficient explanation.

I have 2 Columns A and B with 50000 Records.
Column A and B contains text (no formula).
In Column A contain all cells information.
In column B contain cell B2500 = test.

I select cell B1 and use key: End - arrow down.

Normally cell cursor stops at the first cell with info (B2500),
but sometimes the cell cursor goes to sheet button,
so I think the column is empty and then delete the column
and thereby customerinformations, in this case B2500 = test.

Is there a way to make the curser able to stop at B2500?

--
Best regards
Joergen Bondesen

"Ken" skrev i en meddelelse
....
Joergen

How are you deleting the column? *If you do as I often do and hit
control space to highlight the column, alt edit delete, you may
inadvertantly delete additional data if you have any cells in column B
merged with cells in A or C since the control space will select any
columns that are merged with column B.

Where is your data that is deleted before it gets deleted? *it seems
like deleting an empty column should not cause any loss of data; and
if end-down arrow takes you all the way to the bottom, the column is
either empty or full, and if it is full of data you would probably
know that.

Ken

On Nov 11, 2:45 pm, "Joergen Bondesen" wrote:
Hi NG

I have an odd problem.

Sometimes I get a spreadsheet with records, e.g. 50000 pcs.

Column 2 seems empty, so I mark cell B1 and use key: End - arrow down.

Normally cell cursor stops at the first cell with info, but sometimes the
cell cursor goes to sheet button, so I think the column is empty and then
delete the column and thereby customerinformations.

Any suggestion to how I can avoid this problem?

--
Best Regards
Joergen Bondesen- Hide quoted text -

- Show quoted text -