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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell appears empty but Excel treates if as if it were not (Ctrl+Do
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell appears empty but Excel treates if as if it were not (Ctr
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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell appears empty but Excel treates if as if it were not (Ctr
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 iansmigger wrote: 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" wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell appears empty but Excel treates if as if it were not (Ctr
Thanks for both of your help.
iansmigger "Dave Peterson" wrote: 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 iansmigger wrote: 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" wrote: 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 -- Dave Peterson |
Reply |
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) |