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

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

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