Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort with a "" formula result
In this example assume:
A1 never had a value and is blank Cell B1 contains the formula =if(a1=0,"",a1) and should result in "" Rows D and E are as follows Row num Value 1 widgit 2 3 If I manually Copy B1 and Paste values in E3(below Widget), then sort by row E with header, I come up with the following result: Row num Value 2 1 widgit 3 B1 should have a result of "", which I would think is the same value as a blank cell, hoever the sort does not group these together (row 2 and 3). Sort also puts the result of row 2 "" ahead of widget in this example. This problem was identified in a much more complex and larger project. The above example is simplified for explantion purposes. In the real project the values from column E are put into a combo box after a sort, and all the blanks come to the top. We could sort z to a , but most users expect a to z sorts as would I. Does anyone know how to paste a value that will sort the same as a blank cell? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort with a "" formula result
If instead of returning a blank you returned a very "large" text
value, like "zzzzz", then this will be sorted to the bottom of the list (well, above your true blanks, anyway). You can apply conditional format to the cell such that if it contains "zzzzz" then colour it white (so that it will appear to be blank), and if you are doing Paste Special then click on Formats as well as Values to maintain this colour. Main drawback is if you are doing arithmetic on the cell or the range - you will need to check for the cell possibly containing "zzzzz", but then you might be checking for "" elsewhere anyway, so you can include this check at the same time. Hope this helps. Pete On Nov 26, 11:48 pm, SteveM wrote: In this example assume: A1 never had a value and is blank Cell B1 contains the formula =if(a1=0,"",a1) and should result in "" Rows D and E are as follows Row num Value 1 widgit 2 3 If I manually Copy B1 and Paste values in E3(below Widget), then sort by row E with header, I come up with the following result: Row num Value 2 1 widgit 3 B1 should have a result of "", which I would think is the same value as a blank cell, hoever the sort does not group these together (row 2 and 3). Sort also puts the result of row 2 "" ahead of widget in this example. This problem was identified in a much more complex and larger project. The above example is simplified for explantion purposes. In the real project the values from column E are put into a combo box after a sort, and all the blanks come to the top. We could sort z to a , but most users expect a to z sorts as would I. Does anyone know how to paste a value that will sort the same as a blank cell? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort with a "" formula result
SteveM,
When you have an IF function in a cell that makes it appear blank, the value of the cell ends up being a zero-length string. So when you paste as values, you are pasting a zero-length string instead of a blank cell. Maybe someone has a better way of doing this, but the only way around this that I can think of is to clear the contents of all the cells in the current selection after pasting. I use this code to do that: Sub ClearBlankCells() Dim prngCell As Range Dim pintCalcMode As XlCalculation pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode End Sub HTH, Conan "SteveM" wrote in message ... In this example assume: A1 never had a value and is blank Cell B1 contains the formula =if(a1=0,"",a1) and should result in "" Rows D and E are as follows Row num Value 1 widgit 2 3 If I manually Copy B1 and Paste values in E3(below Widget), then sort by row E with header, I come up with the following result: Row num Value 2 1 widgit 3 B1 should have a result of "", which I would think is the same value as a blank cell, hoever the sort does not group these together (row 2 and 3). Sort also puts the result of row 2 "" ahead of widget in this example. This problem was identified in a much more complex and larger project. The above example is simplified for explantion purposes. In the real project the values from column E are put into a combo box after a sort, and all the blanks come to the top. We could sort z to a , but most users expect a to z sorts as would I. Does anyone know how to paste a value that will sort the same as a blank cell? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort with a "" formula result
sorry!!!
Maybe someone has a better way of doing this, but the only way around this that I can think of is to clear the contents of all the cells in the current selection after pasting. I use this code to do that: Should read: Maybe someone has a better way of doing this, but the only way around this that I can think of is to clear the contents of all the cells in the current selection THAT APPEAR BLANK after pasting. I use this code to do that: sorry for leaving that out. "Conan Kelly" wrote in message ... SteveM, When you have an IF function in a cell that makes it appear blank, the value of the cell ends up being a zero-length string. So when you paste as values, you are pasting a zero-length string instead of a blank cell. Maybe someone has a better way of doing this, but the only way around this that I can think of is to clear the contents of all the cells in the current selection after pasting. I use this code to do that: Sub ClearBlankCells() Dim prngCell As Range Dim pintCalcMode As XlCalculation pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode End Sub HTH, Conan "SteveM" wrote in message ... In this example assume: A1 never had a value and is blank Cell B1 contains the formula =if(a1=0,"",a1) and should result in "" Rows D and E are as follows Row num Value 1 widgit 2 3 If I manually Copy B1 and Paste values in E3(below Widget), then sort by row E with header, I come up with the following result: Row num Value 2 1 widgit 3 B1 should have a result of "", which I would think is the same value as a blank cell, hoever the sort does not group these together (row 2 and 3). Sort also puts the result of row 2 "" ahead of widget in this example. This problem was identified in a much more complex and larger project. The above example is simplified for explantion purposes. In the real project the values from column E are put into a combo box after a sort, and all the blanks come to the top. We could sort z to a , but most users expect a to z sorts as would I. Does anyone know how to paste a value that will sort the same as a blank cell? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort with a "" formula result
Conan, I think your idea should work , but I am getting a PasteSpecial method
of class failed error with the following code. It worked prior to adding the lines you specified. Range("Data_to_Save").Select Selection.Copy Sheets("Client Data").Activate pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=True, Transpose:=True "Conan Kelly" wrote: SteveM, When you have an IF function in a cell that makes it appear blank, the value of the cell ends up being a zero-length string. So when you paste as values, you are pasting a zero-length string instead of a blank cell. Maybe someone has a better way of doing this, but the only way around this that I can think of is to clear the contents of all the cells in the current selection after pasting. I use this code to do that: Sub ClearBlankCells() Dim prngCell As Range Dim pintCalcMode As XlCalculation pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode End Sub HTH, Conan "SteveM" wrote in message ... In this example assume: A1 never had a value and is blank Cell B1 contains the formula =if(a1=0,"",a1) and should result in "" Rows D and E are as follows Row num Value 1 widgit 2 3 If I manually Copy B1 and Paste values in E3(below Widget), then sort by row E with header, I come up with the following result: Row num Value 2 1 widgit 3 B1 should have a result of "", which I would think is the same value as a blank cell, hoever the sort does not group these together (row 2 and 3). Sort also puts the result of row 2 "" ahead of widget in this example. This problem was identified in a much more complex and larger project. The above example is simplified for explantion purposes. In the real project the values from column E are put into a combo box after a sort, and all the blanks come to the top. We could sort z to a , but most users expect a to z sorts as would I. Does anyone know how to paste a value that will sort the same as a blank cell? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort with a "" formula result
It appears to be working with the following:
Sheets("Client Info").Select Range("Data_to_Save").Select pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode Selection.Copy Sheets("Client Data").Activate Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Thanks for your help "SteveM" wrote: Conan, I think your idea should work , but I am getting a PasteSpecial method of class failed error with the following code. It worked prior to adding the lines you specified. Range("Data_to_Save").Select Selection.Copy Sheets("Client Data").Activate pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=True, Transpose:=True "Conan Kelly" wrote: SteveM, When you have an IF function in a cell that makes it appear blank, the value of the cell ends up being a zero-length string. So when you paste as values, you are pasting a zero-length string instead of a blank cell. Maybe someone has a better way of doing this, but the only way around this that I can think of is to clear the contents of all the cells in the current selection after pasting. I use this code to do that: Sub ClearBlankCells() Dim prngCell As Range Dim pintCalcMode As XlCalculation pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode End Sub HTH, Conan "SteveM" wrote in message ... In this example assume: A1 never had a value and is blank Cell B1 contains the formula =if(a1=0,"",a1) and should result in "" Rows D and E are as follows Row num Value 1 widgit 2 3 If I manually Copy B1 and Paste values in E3(below Widget), then sort by row E with header, I come up with the following result: Row num Value 2 1 widgit 3 B1 should have a result of "", which I would think is the same value as a blank cell, hoever the sort does not group these together (row 2 and 3). Sort also puts the result of row 2 "" ahead of widget in this example. This problem was identified in a much more complex and larger project. The above example is simplified for explantion purposes. In the real project the values from column E are put into a combo box after a sort, and all the blanks come to the top. We could sort z to a , but most users expect a to z sorts as would I. Does anyone know how to paste a value that will sort the same as a blank cell? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort with a "" formula result
SteveM,
It looks like you are running my code on the original data. Doing that would probably delete the formulas that are causing the cell to appear blank.......turning them into actual blank cells..........and then copying-pasting as values afterwards. I was thinking more like copying, pasting as values, then running the code to loop through each of the "pasted as values" cells. That will keep your IF() functions in tact. After pasting as values, the pasted data should be selected, so there should be no need to figure out what data needs to be selected and reselecting it. Just run the "loop through each cell" code to clear the contents of the cells that appear blank. Something like this: Sheets("Client Info").Select Range("Data_to_Save").Select Selection.Copy Sheets("Client Data").Activate Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode HTH, Conan "SteveM" wrote in message ... It appears to be working with the following: Sheets("Client Info").Select Range("Data_to_Save").Select pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode Selection.Copy Sheets("Client Data").Activate Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Thanks for your help "SteveM" wrote: Conan, I think your idea should work , but I am getting a PasteSpecial method of class failed error with the following code. It worked prior to adding the lines you specified. Range("Data_to_Save").Select Selection.Copy Sheets("Client Data").Activate pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=True, Transpose:=True "Conan Kelly" wrote: SteveM, When you have an IF function in a cell that makes it appear blank, the value of the cell ends up being a zero-length string. So when you paste as values, you are pasting a zero-length string instead of a blank cell. Maybe someone has a better way of doing this, but the only way around this that I can think of is to clear the contents of all the cells in the current selection after pasting. I use this code to do that: Sub ClearBlankCells() Dim prngCell As Range Dim pintCalcMode As XlCalculation pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode End Sub HTH, Conan "SteveM" wrote in message ... In this example assume: A1 never had a value and is blank Cell B1 contains the formula =if(a1=0,"",a1) and should result in "" Rows D and E are as follows Row num Value 1 widgit 2 3 If I manually Copy B1 and Paste values in E3(below Widget), then sort by row E with header, I come up with the following result: Row num Value 2 1 widgit 3 B1 should have a result of "", which I would think is the same value as a blank cell, hoever the sort does not group these together (row 2 and 3). Sort also puts the result of row 2 "" ahead of widget in this example. This problem was identified in a much more complex and larger project. The above example is simplified for explantion purposes. In the real project the values from column E are put into a combo box after a sort, and all the blanks come to the top. We could sort z to a , but most users expect a to z sorts as would I. Does anyone know how to paste a value that will sort the same as a blank cell? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort with a "" formula result
Thanks Conan, The range I copied from all receive controlsource data, so
there are no formulas, but I think the way you have it here is cleaner. One final question, I tried to modefy the line (If prngCell = "" Then) to (If prngCell = "" or 0 Then) but it did not clear the 0's. I also tried "0" without success. Any thoughts. Steve "Conan Kelly" wrote: SteveM, It looks like you are running my code on the original data. Doing that would probably delete the formulas that are causing the cell to appear blank.......turning them into actual blank cells..........and then copying-pasting as values afterwards. I was thinking more like copying, pasting as values, then running the code to loop through each of the "pasted as values" cells. That will keep your IF() functions in tact. After pasting as values, the pasted data should be selected, so there should be no need to figure out what data needs to be selected and reselecting it. Just run the "loop through each cell" code to clear the contents of the cells that appear blank. Something like this: Sheets("Client Info").Select Range("Data_to_Save").Select Selection.Copy Sheets("Client Data").Activate Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode HTH, Conan "SteveM" wrote in message ... It appears to be working with the following: Sheets("Client Info").Select Range("Data_to_Save").Select pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode Selection.Copy Sheets("Client Data").Activate Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Thanks for your help "SteveM" wrote: Conan, I think your idea should work , but I am getting a PasteSpecial method of class failed error with the following code. It worked prior to adding the lines you specified. Range("Data_to_Save").Select Selection.Copy Sheets("Client Data").Activate pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=True, Transpose:=True "Conan Kelly" wrote: SteveM, When you have an IF function in a cell that makes it appear blank, the value of the cell ends up being a zero-length string. So when you paste as values, you are pasting a zero-length string instead of a blank cell. Maybe someone has a better way of doing this, but the only way around this that I can think of is to clear the contents of all the cells in the current selection after pasting. I use this code to do that: Sub ClearBlankCells() Dim prngCell As Range Dim pintCalcMode As XlCalculation pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode End Sub HTH, Conan "SteveM" wrote in message ... In this example assume: A1 never had a value and is blank Cell B1 contains the formula =if(a1=0,"",a1) and should result in "" Rows D and E are as follows Row num Value 1 widgit 2 3 If I manually Copy B1 and Paste values in E3(below Widget), then sort by row E with header, I come up with the following result: Row num Value 2 1 widgit 3 B1 should have a result of "", which I would think is the same value as a blank cell, hoever the sort does not group these together (row 2 and 3). Sort also puts the result of row 2 "" ahead of widget in this example. This problem was identified in a much more complex and larger project. The above example is simplified for explantion purposes. In the real project the values from column E are put into a combo box after a sort, and all the blanks come to the top. We could sort z to a , but most users expect a to z sorts as would I. Does anyone know how to paste a value that will sort the same as a blank cell? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort with a "" formula result
SteveM,
Try this: If prngCell = "" OR prngCell = 0 Then In an if statment (or any other statement), you can not compare one item to 2 different values. You can only compare one item to one value. If you need to compare one item to 2 different values, you need to compare it to those values separately......you need the item listed twice with a comparison to each instance of it. HTH, Conan "SteveM" wrote in message ... Thanks Conan, The range I copied from all receive controlsource data, so there are no formulas, but I think the way you have it here is cleaner. One final question, I tried to modefy the line (If prngCell = "" Then) to (If prngCell = "" or 0 Then) but it did not clear the 0's. I also tried "0" without success. Any thoughts. Steve "Conan Kelly" wrote: SteveM, It looks like you are running my code on the original data. Doing that would probably delete the formulas that are causing the cell to appear blank.......turning them into actual blank cells..........and then copying-pasting as values afterwards. I was thinking more like copying, pasting as values, then running the code to loop through each of the "pasted as values" cells. That will keep your IF() functions in tact. After pasting as values, the pasted data should be selected, so there should be no need to figure out what data needs to be selected and reselecting it. Just run the "loop through each cell" code to clear the contents of the cells that appear blank. Something like this: Sheets("Client Info").Select Range("Data_to_Save").Select Selection.Copy Sheets("Client Data").Activate Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode HTH, Conan "SteveM" wrote in message ... It appears to be working with the following: Sheets("Client Info").Select Range("Data_to_Save").Select pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode Selection.Copy Sheets("Client Data").Activate Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Thanks for your help "SteveM" wrote: Conan, I think your idea should work , but I am getting a PasteSpecial method of class failed error with the following code. It worked prior to adding the lines you specified. Range("Data_to_Save").Select Selection.Copy Sheets("Client Data").Activate pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode Range("c2").Select ActiveCell.Offset(rowOffset:=Range("Index_Num")).A ctivate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=True, Transpose:=True "Conan Kelly" wrote: SteveM, When you have an IF function in a cell that makes it appear blank, the value of the cell ends up being a zero-length string. So when you paste as values, you are pasting a zero-length string instead of a blank cell. Maybe someone has a better way of doing this, but the only way around this that I can think of is to clear the contents of all the cells in the current selection after pasting. I use this code to do that: Sub ClearBlankCells() Dim prngCell As Range Dim pintCalcMode As XlCalculation pintCalcMode = Application.Calculation Application.Calculation = xlCalculationManual For Each prngCell In Selection If prngCell = "" Then prngCell.ClearContents End If Next prngCell Application.Calculation = pintCalcMode End Sub HTH, Conan "SteveM" wrote in message ... In this example assume: A1 never had a value and is blank Cell B1 contains the formula =if(a1=0,"",a1) and should result in "" Rows D and E are as follows Row num Value 1 widgit 2 3 If I manually Copy B1 and Paste values in E3(below Widget), then sort by row E with header, I come up with the following result: Row num Value 2 1 widgit 3 B1 should have a result of "", which I would think is the same value as a blank cell, hoever the sort does not group these together (row 2 and 3). Sort also puts the result of row 2 "" ahead of widget in this example. This problem was identified in a much more complex and larger project. The above example is simplified for explantion purposes. In the real project the values from column E are put into a combo box after a sort, and all the blanks come to the top. We could sort z to a , but most users expect a to z sorts as would I. Does anyone know how to paste a value that will sort the same as a blank cell? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when a "check box" is checked, a "result" to be shown in another c | Excel Discussion (Misc queries) | |||
need to substitute formula result from #### to"CHECK YOUR ENTRY" | Excel Discussion (Misc queries) | |||
How can I find cell contains the result of "maximum" formula | Excel Discussion (Misc queries) | |||
pictures to work with "data" "sort" option | Excel Discussion (Misc queries) | |||
How do I replace a "#N/A" formula result with a blank in excel? | Excel Discussion (Misc queries) |