Cell appears empty but Excel treates if as if it were not (Ctrl+Do

G

Guest

I hope someone can help me fathom this one out.

I have a macro in Excel (2000) which inserts a column and copies the formula
=IF('cell ref'>50000,"1","") down. It then highlights the column with the
formula in it and Copy, Paste Special, Values in order to remove the formula
and leave all cells blank unless the cell referred to is greater than 50,000.

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[8]>50000,""1"","""")"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A65536")
Range("A2:A65536").Select
Calculate
Columns("A:A").Select
Selection.copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

The next line of code is supposed to delete all rows with a blank in this
column:

Columns("A:A").Select
On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

However, the rows with blank cells are not deleted whereas the code works
fine in other columns that are processing the static data (i.e. generated
from a report which has blank cells).

On closer examination (baffled as to why the rows were not being deleted)
when you select the top of the column A with a 1 in it and press CTRL+Down,
instead of taking you to the cell above the first empty cell, you are taken
to last cell that had the IF formula in it, even though it returned a blank.
This is the only explanation I can see as to why these rows are not being
deleted.

Any help would be appreciated.

iansmigger
 
J

Jim Cone

To get around the problem, instead of deleting just sort the column.
Excel sorts numbers before text...
Columns("A").Sort key1:=Columns("A"), order1:=xlAscending
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"iansmigger" <[email protected]>
wrote in message
I hope someone can help me fathom this one out.
I have a macro in Excel (2000) which inserts a column and copies the formula
=IF('cell ref'>50000,"1","") down. It then highlights the column with the
formula in it and Copy, Paste Special, Values in order to remove the formula
and leave all cells blank unless the cell referred to is greater than 50,000.

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[8]>50000,""1"","""")"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A65536")
Range("A2:A65536").Select
Calculate
Columns("A:A").Select
Selection.copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

The next line of code is supposed to delete all rows with a blank in this
column:
Columns("A:A").Select
On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

However, the rows with blank cells are not deleted whereas the code works
fine in other columns that are processing the static data (i.e. generated
from a report which has blank cells).
On closer examination (baffled as to why the rows were not being deleted)
when you select the top of the column A with a 1 in it and press CTRL+Down,
instead of taking you to the cell above the first empty cell, you are taken
to last cell that had the IF formula in it, even though it returned a blank.
This is the only explanation I can see as to why these rows are not being
deleted.
Any help would be appreciated.
iansmigger
 
G

Guest

Thanks but due to the volme of data being processed (30,000+ rows) and
various sorts accross other columns, the unwanted data needs to be deleted.
 
J

Jim Cone

Maybe this...

Sub NotEnoughThere()
Dim rCell As Range
Dim rng As Range
Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp))
For Each rCell In rng
If IsNumeric(rCell.Value) Then
If rCell.Value <= 50000 Then rCell.EntireRow.Clear
End If
Next
rng.Sort key1:=rng, order1:=xlAscending
Set rCell = Nothing
Set rng = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"iansmigger" <
(e-mail address removed)>
wrote in message
Thanks but due to the volme of data being processed (30,000+ rows) and
various sorts accross other columns, the unwanted data needs to be deleted.
 
D

Dave Peterson

When you put the formulas in the cells and converted them to values, then cells
that looked blank aren't really empty.

But maybe you could use the same technique but with a different .specialcells
option:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim DummyRng As Range
Dim LastRow As Long

Set wks = ActiveSheet

With wks
'try to reset last used cell
Set DummyRng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

.Columns(1).Insert

'fill the column--just in the .usedrange, though
With .Range("a1:a" & LastRow)
.FormulaR1C1 = "=IF(RC[8]>50000,1,na())"
.Calculate 'just in case
.Value = .Value
On Error Resume Next
.Cells.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
End With

.Columns(1).Delete
Set DummyRng = .UsedRange
End With
End Sub

And some info:

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all
Thanks but due to the volme of data being processed (30,000+ rows) and
various sorts accross other columns, the unwanted data needs to be deleted.

Jim Cone said:
To get around the problem, instead of deleting just sort the column.
Excel sorts numbers before text...
Columns("A").Sort key1:=Columns("A"), order1:=xlAscending
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"iansmigger" <[email protected]>
wrote in message
I hope someone can help me fathom this one out.
I have a macro in Excel (2000) which inserts a column and copies the formula
=IF('cell ref'>50000,"1","") down. It then highlights the column with the
formula in it and Copy, Paste Special, Values in order to remove the formula
and leave all cells blank unless the cell referred to is greater than 50,000.

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[8]>50000,""1"","""")"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A65536")
Range("A2:A65536").Select
Calculate
Columns("A:A").Select
Selection.copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

The next line of code is supposed to delete all rows with a blank in this
column:
Columns("A:A").Select
On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

However, the rows with blank cells are not deleted whereas the code works
fine in other columns that are processing the static data (i.e. generated
from a report which has blank cells).
On closer examination (baffled as to why the rows were not being deleted)
when you select the top of the column A with a 1 in it and press CTRL+Down,
instead of taking you to the cell above the first empty cell, you are taken
to last cell that had the IF formula in it, even though it returned a blank.
This is the only explanation I can see as to why these rows are not being
deleted.
Any help would be appreciated.
iansmigger
Click to expand...
Click to expand...
 
G

Guest

Thanks for both of your help.

iansmigger

Dave Peterson said:
When you put the formulas in the cells and converted them to values, then cells
that looked blank aren't really empty.

But maybe you could use the same technique but with a different .specialcells
option:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim DummyRng As Range
Dim LastRow As Long

Set wks = ActiveSheet

With wks
'try to reset last used cell
Set DummyRng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

.Columns(1).Insert

'fill the column--just in the .usedrange, though
With .Range("a1:a" & LastRow)
.FormulaR1C1 = "=IF(RC[8]>50000,1,na())"
.Calculate 'just in case
.Value = .Value
On Error Resume Next
.Cells.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
End With

.Columns(1).Delete
Set DummyRng = .UsedRange
End With
End Sub

And some info:

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all
Thanks but due to the volme of data being processed (30,000+ rows) and
various sorts accross other columns, the unwanted data needs to be deleted.

Jim Cone said:
To get around the problem, instead of deleting just sort the column.
Excel sorts numbers before text...
Columns("A").Sort key1:=Columns("A"), order1:=xlAscending
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"iansmigger" <[email protected]>
wrote in message
I hope someone can help me fathom this one out.
I have a macro in Excel (2000) which inserts a column and copies the formula
=IF('cell ref'>50000,"1","") down. It then highlights the column with the
formula in it and Copy, Paste Special, Values in order to remove the formula
and leave all cells blank unless the cell referred to is greater than 50,000.

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[8]>50000,""1"","""")"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A65536")
Range("A2:A65536").Select
Calculate
Columns("A:A").Select
Selection.copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

The next line of code is supposed to delete all rows with a blank in this
column:
Columns("A:A").Select
On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

However, the rows with blank cells are not deleted whereas the code works
fine in other columns that are processing the static data (i.e. generated
from a report which has blank cells).
On closer examination (baffled as to why the rows were not being deleted)
when you select the top of the column A with a 1 in it and press CTRL+Down,
instead of taking you to the cell above the first empty cell, you are taken
to last cell that had the IF formula in it, even though it returned a blank.
This is the only explanation I can see as to why these rows are not being
deleted.
Any help would be appreciated.
iansmigger
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

moving all data to cell a1 4
2 macro help 7
to change the macros as per active cell and range 2
After Macro runs, no cell activated - what am I missing? 2
Sort by column, not range 1
Hide Column If Usedrange of Column ISBLANK Follow-up 1
can't paste from clipboard in my macro 1
Delete empty rows with cell type 2 1

Top