Consulting

Results 1 to 3 of 3

Thread: Solved: Change From Print (Range) To Select (Range)

  1. #1

    Solved: Change From Print (Range) To Select (Range)

    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


    [VBA]Sheets("inventory").Select
    Range("A2650").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[/VBA]
    Last edited by mark007; 06-04-2004 at 12:35 PM. Reason: Check out the VBA tags for formatting code :)

  2. #2
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi Kurt,

    Welcome to the forum

    Is this the sort of thing you mean:[vba]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" & 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[/vba]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

  3. #3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •