Change From Print (Range) To Select (Range)

[stapuff]

After sorting the negative numbers to the top -this code loops through the sheet to the last row of negative numbers then send it to the printer. Works great, but I want to change it to select the negative number range rather than print it so I can copy and paste to another sheet.

Any help?


Thanks,


Kurt

Sheets("inventory").Select
    Range("A2:D650").Select
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
        xlSortNormal
    Range("c65536").End(xlUp).Select
While ActiveCell >= 0
    ActiveCell.Offset(-1, 0).Select
Wend
LastRow = ActiveCell.Row
ActiveSheet.PageSetup.PrintArea = "$a$2:$d$" & LastRow
'Application.ActivePrinter = "\\EARTH\Prodlaser on Ne02:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\EARTH\Prodlaser on Ne02:", Collate:=True

[Richie(UK)]

Hi Kurt,

Welcome to the forum

Is this the sort of thing you mean:

Sub Test()
    Dim rng As Range, lLastRow As Long, rngCell As Range
With Sheets("inventory")
    lLastRow = .Cells(Rows.Count, "C").End(xlUp).Row
    Set rng = .Range("A2:D" & lLastRow)
    rng.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("D2") _
    , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
    'do the sort
    Set rng = .Range("C2:C" & lLastRow)
    lLastRow = 0
    For Each rngCell In rng
        If rngCell.Value >= 0 Then
            lLastRow = rngCell.Row
            Exit For
        End If
    Next rngCell
    'find first positive
    If lLastRow > 0 Then
        Set rng = .Range("C2:C" & lLastRow - 1)
        MsgBox rng.Address
    End If
    'set the negative range
End With
End Sub

Note that it is rarely necessary to select or activate objects in order to work with them - and your code will run much quicker if you can avoid such actions.

HTH

[stapuff]

Richie -

Thanks for the post back. Apologize for the delay. Seems to work just fine. As far as the select and activate - pretty new to coding. I have not really notice a huge slow down compare to code without them, however, I need to get into the practice of doing things correctly (once I know what that is). Folks like yourself teach me daily. As a subscriber to 3 excel forumn's now - education is starting to come quickly and I thank you all.


Kurt