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"
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).Enti reRow.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