Consulting

Results 1 to 3 of 3

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

  1. #1

    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


    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
    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:

    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

  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
  •