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 (file://\EARTHProdlaser) on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\EARTH\Prodlaser (file://\EARTHProdlaser) 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