Blank Cells when Sorting

[Xiazer]

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?

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Xiazer]

Top Notch! Worked out great! thanks and thanks again.