Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank Cells when Sorting
I am trying to sort a list of users. The way I have it set up is in on list people who have license agreements are highlighted green. That wa easy. I need to pull out ONLY the people who have been highlighted an sort them in another column. The way I do this is in another column like "M" and "N" I have th conditional formatting equation which is =IF(A3="","",ISNUMBER(MATCH(A3,B:B,0))) that will show TRUE or False. In the next column over I have a equation to pull the name from the original row I am sorting. =IF(M3="","",IF(M3=TRUE,A3,"")) Then I tie it to a "Sorting" button to paste special, values only, int Column C and automatically sort it. Private Sub sortuser_Click() Dim deleter As Integer Range("N2:N30").Copy Range("C2:C30").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone SkipBlanks _ :=True, Transpose:=False Range("a1").Select Range("C2:C30").Sort Key1:=Range("C2"), Order1:=xlAscending Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub but whenever it sorts there are blank spaces it copies and sorts the to the top. but If i delete the contents, even though there isn' anything in the cell, and sort it, it will sort will the first name i Cell C2(where is should be) Even when I paste special with ignor blanks it still does it, any Ideas -- Xiaze ----------------------------------------------------------------------- Xiazer's Profile: http://www.excelforum.com/member.php...fo&userid=3158 View this thread: http://www.excelforum.com/showthread.php?threadid=51728 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank Cells when Sorting
change your formula to
=IF(A3="",na(),ISNUMBER(MATCH(A3,B:B,0))) then Private Sub sortuser_Click() Dim deleter As Integer On Error Resume next Range("N2:N30").SpecialCells(xlFormulas, _ xlerrors).ClearContents On Error goto 0 Range("N2:N30").Copy Range("C2:C30").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=True, Transpose:=False Range("a1").Select Range("C2:C30").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub -- Regards, Tom Ogilvy "Xiazer" wrote in message ... I am trying to sort a list of users. The way I have it set up is in one list people who have license agreements are highlighted green. That was easy. I need to pull out ONLY the people who have been highlighted and sort them in another column. The way I do this is in another column like "M" and "N" I have the conditional formatting equation which is =IF(A3="","",ISNUMBER(MATCH(A3,B:B,0))) that will show TRUE or False. In the next column over I have an equation to pull the name from the original row I am sorting. =IF(M3="","",IF(M3=TRUE,A3,"")) Then I tie it to a "Sorting" button to paste special, values only, into Column C and automatically sort it. Private Sub sortuser_Click() Dim deleter As Integer Range("N2:N30").Copy Range("C2:C30").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=True, Transpose:=False Range("a1").Select Range("C2:C30").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub but whenever it sorts there are blank spaces it copies and sorts them to the top. but If i delete the contents, even though there isn't anything in the cell, and sort it, it will sort will the first name in Cell C2(where is should be) Even when I paste special with ignore blanks it still does it, any Ideas? -- Xiazer ------------------------------------------------------------------------ Xiazer's Profile: http://www.excelforum.com/member.php...o&userid=31581 View this thread: http://www.excelforum.com/showthread...hreadid=517285 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank Cells when Sorting
Top Notch! Worked out great! thanks and thanks again. -- Xiazer ------------------------------------------------------------------------ Xiazer's Profile: http://www.excelforum.com/member.php...o&userid=31581 View this thread: http://www.excelforum.com/showthread...hreadid=517285 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting blank cells with a formula in them | Excel Discussion (Misc queries) | |||
remove blank cells - no sorting | Excel Worksheet Functions | |||
Sorting Blank Cells | Excel Discussion (Misc queries) | |||
Sorting with blank cells | Excel Discussion (Misc queries) | |||
Sorting and Blank Cells | Excel Worksheet Functions |