Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell appears empty but Excel treates if as if it were not (Ctrl+Do
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - Ctrl-End selects last cell. How chg last cell addr? | Excel Worksheet Functions | |||
How do I change the end cell (Ctrl end) in a excel spreadsheet? | Excel Worksheet Functions | |||
Error can't empty clipboard appears when trying to copy. | Excel Discussion (Misc queries) | |||
Error can't empty clipboard appears when trying to copy. | Excel Discussion (Misc queries) | |||
How do i use symbols (e.g. X,Y,Z) so that outlook treates them li. | Excel Discussion (Misc queries) |