Excel 2002 : Problem in moving along blank cells

[Mr. Low]

Dear Sir,


I have a column of formulas that was converted back to values.

However it does not have the properties of a blank cell whereby I can move
to the next filled cell by CTRL + Arrow Up.

It went to the very end of the column instead of the next filled cell when I
do so

Illustration :

A B C

1xxx xxxx 256 <-column C was a formula column before converting to
values
2 211
3 321
4
5

35


C4: C35 are blank cells Ҡin the foumulas. CTRL + Arrow Up at C35 moved
the cursor to C1 instead of C3. Also when the cursor is at C1 CTRL + Arrow
Down moves the cursor to C35 instead of C3 (the last filled cell).

I find that this function usually work for the non formularized cells and
not for the cells that is conveted from formulas to values.

May I know how to overcome this problem ?



Thanks

Low

 

[Jim Rech]

C4: C35 are blank cells

Actually they are not 'blank'. They have "" in them. In the old days we
referred to these as 'null cells' and that's still a good name even if it's
not used much. If you convert formulas that return "" to values you will
get null cells.

The only solution I know of is a macro. Here's one I wrote for someone
years ago:

Sub ClearNullsInSelection()
Dim CurrCell As Range, CurrCol As Range
Dim EraseRg As Range
Dim NullCounter As Long
For Each CurrCol In Selection.Columns
Application.StatusBar = "Doing column " & CurrCol.Address
For Each CurrCell In CurrCol.SpecialCells(xlCellTypeConstants)
If Len(CurrCell.Formula) = 0 And Not IsEmpty(CurrCell) Then
NullCounter = NullCounter + 1
If EraseRg Is Nothing Then
Set EraseRg = CurrCell
Else
Set EraseRg = Union(EraseRg, CurrCell)
End If
End If
Next
If Not EraseRg Is Nothing Then
EraseRg.ClearContents
Set EraseRg = Nothing
End If
Next
Application.StatusBar = False
If NullCounter > 0 Then
MsgBox NullCounter & " null cells cleared"
Else
MsgBox "No null cells found"
End If
End Sub

--
Jim
| Dear Sir,
|
|
| I have a column of formulas that was converted back to values.
|
| However it does not have the properties of a blank cell whereby I can move
| to the next filled cell by CTRL + Arrow Up.
|
| It went to the very end of the column instead of the next filled cell when
I
| do so
|
| Illustration :
|
| A B C
|
| 1xxx xxxx 256 <-column C was a formula column before converting
to
| values
| 2 211
| 3 321
| 4
| 5
|
| 35
|
|
| C4: C35 are blank cells "" in the foumulas. CTRL + Arrow Up at C35 moved
| the cursor to C1 instead of C3. Also when the cursor is at C1 CTRL + Arrow
| Down moves the cursor to C35 instead of C3 (the last filled cell).
|
| I find that this function usually work for the non formularized cells and
| not for the cells that is conveted from formulas to values.
|
| May I know how to overcome this problem ?
|
|
|
| Thanks
|
| Low
|
|
| --
| A36B58K641

 

[Dave Peterson]

Another way to do this is (after the formulas have been converted to values):

Select the range to fix
edit|replace
what: (leave blank)
with: $$$$$
replace all

And then reverse it

edit|replace
what: $$$$$
with: (leave blank)
replace all

If you needed a macro, you could record one when you did it manually.

 

[Mr. Low]

Hello Jim

Thanks for your codes.

Kind Regards

Low

 

[Mr. Low]

Hello Dave,

I am not able to perform the your steps.

The error message is "MS Excel cannot find any data to replace, check if
your search formatting criteria are defined correctly. If you are sure that
matching data exist in this worksheet, it may be on a protected sheet. Excel
cannot replace data on a protected sheetâ€

I have confirmed that the worksheet is unprotected.

May I know how to overcome this problem ?

Thanks

Low

 

[Dave Peterson]

I thought you had put formulas that evaluated to "" in a range. Then you
converted to values--so you ended up with a zero length string in those cells.

Did I misunderstand?

Hello Dave,

I am not able to perform the your steps.

The error message is "MS Excel cannot find any data to replace, check if
your search formatting criteria are defined correctly. If you are sure that
matching data exist in this worksheet, it may be on a protected sheet. Excel
cannot replace data on a protected sheetâ€

I have confirmed that the worksheet is unprotected.

May I know how to overcome this problem ?

Thanks

Low

 

[Mr. Low]

Hello Dave,

My situation is I have a formula say =IF(A2>6,A2*2.5,"") at C2. I copy it
down to C
12 , I may get answers in some cells but others are empty.

If I convert it to value using copy and paste special, those blank cells
does not have the properties of blank cells as in other unused cells.

I hope you can have a clearer picture now.

Thanks

Low

 

[Dave Peterson]

I've used that first suggestion lots of times.

Make sure you've done what you said.

Copy the formula into each cell in the range
convert that range to values
check to see if any of the cells look empty (evaluate to "")
Then with that range selected, try the pair of edit|replace commands.

It's always worked for me.

Hello Dave,

My situation is I have a formula say =IF(A2>6,A2*2.5,"") at C2. I copy it
down to C
12 , I may get answers in some cells but others are empty.

If I convert it to value using copy and paste special, those blank cells
does not have the properties of blank cells as in other unused cells.

I hope you can have a clearer picture now.

Thanks

Low

 

[Mr. Low]

Hello Dave

May I know what do you mean by "check to see if any of the cells look empty
(evaluate to "")"

When the cells that do not have any answer after the formula entry, it
looks just like any other blank cells. Is it what you mean ? ( I cannot see
the "" within the cell)

Thanks

Low

 

[Dave Peterson]

Yes. That's what I meant.

Hello Dave

May I know what do you mean by "check to see if any of the cells look empty
(evaluate to "")"

When the cells that do not have any answer after the formula entry, it
looks just like any other blank cells. Is it what you mean ? ( I cannot see
the "" within the cell)

Thanks

Low

--
A36B58K641

I've used that first suggestion lots of times.

Make sure you've done what you said.

Copy the formula into each cell in the range
convert that range to values
check to see if any of the cells look empty (evaluate to "")
Then with that range selected, try the pair of edit|replace commands.

It's always worked for me.

 

[Mr. Low]

Hello Jim,

I have tested the code you provided today.

It runs very smoothly and has done the job perfectly well.

Many thanks

Low