Results 1 to 11 of 11
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Maco Help (95/97/2000)

    In performance sheets for departments I have macro's printing the whole sheet, and then a line per page to give to the staff. The number of rows for staff is the same for each sheet, row 14 - row 33. How can I set the macro to test column CRow14 and if not blank print, then move to the next row. If blank move down a row without printing, test the next and either print or move down until row 33 is reached?
    Thank you
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Maco Help (95/97/2000)

    I'm a little unclear on which columns you are testing for blanks, but perhaps this will get you started:

    Sub PrintRows()
    Dim rngCell As Range
    For Each rngCell In Selection.Cells
    If rngCell.Value <> "" Then
    ActiveSheet.PageSetup.PrintArea = rngCell.EntireRow.Address
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End If
    Next rngCell
    End Sub

    You'll have to modify Selection.Cells to set to the range or column you want to run this on. Oh, and I think this shortcuts by assuming that your print range will never be more than the width of the default Page Setup.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Maco Help (95/97/2000)

    You could also use a data filter to hide the blank rows prior to printing.

    I have a big long price list that the users enter quantities on. Then they click a button that limits the visible rows to only those where Quantity>0. The button is actually a toggle that either hides or shows the "blank" rows. I don't recommend using Autofilter -- buggy results and funky little drop-down arrows in the column heads -- I just coded my own filter in the macro and apply it or remove it.

    Then what you see is what you get when you print.

    This is basically the macro attached to the button:

    If ActiveSheet.FilterMode Then

    ActiveSheet.ShowAllData
    ActiveSheet.Shapes("ViewButton").Select
    Selection.Characters.Text = "Short List"
    Range("D7").Select

    Else

    Range("data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Worksheet!Criteria"), Unique:=False
    ActiveSheet.Shapes("ViewButton").Select
    Selection.Characters.Text = "Long View"
    Range("D7").Select

    End If
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Maco Help (95/97/2000)

    You are right you can do this with Advanced Filter, it's just that I prefer to leave the original data and have a sub set of that data to copy or move. I must have made a typo as I meant advanced filter and not autofilter, as I agree with your comments re the arrow heads etc
    <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Paul Coyle
    Approach love and cooking with reckless abandon

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Maco Help (95/97/2000)

    Thanks John I will trial this.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  6. #6
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Maco Help (95/97/2000)

    Hi John, I need more help, as I am a real tyro with VBA. I tried your solution on a blank sheet and got it to work - sort of, but had a couple of problems that relates to my lack of knowledge
    I thought it best to post a cut down version of the s/sheet so you can see what I am trying to do. Column C is the test column, from rows 14 - 33. If no minutes are entered in column C then there is no need to print out the row details as that person was not at work.
    Attached Files Attached Files
    Paul Coyle
    Approach love and cooking with reckless abandon

  7. #7
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Maco Help (95/97/2000)

    While your waiting, see what you think of this method.
    Since you have headers and total rows where column C is blank you don't want to hide ALL rows where C is blank, just those in the data area of your spreadsheet. So give that area a range name. It'll grow as you add new employees to the sheet. Or you can just specify the exact rows to "trim down".

    <pre>Sub PrintNonBlankDataRows()
    'These two rows figure out which rows to check for blanks
    'You need to select the print area to "trim down" and name it Data
    FirstRow = Range("Data").Row
    For iRow = FirstRow To FirstRow + Range("data").Rows.Count
    'If you just want to specify rows by number, use comment out lines above and use this line:
    'For iRow = 14 To 36

    If Range("C" & iRow) = "" Then
    Rows(iRow).EntireRow.Hidden = True
    End If
    Next iRow

    'Print or print preview
    ActiveWindow.SelectedSheets.PrintPreview

    'Unhide rows
    ActiveSheet.Rows.EntireRow.Hidden = False

    End Sub
    </pre>

    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Maco Help (95/97/2000)

    This should work for you. Check that I reset the paper to ISO A4.
    .
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Maco Help (95/97/2000)

    Works a treat, both me and the user are rapt. Many thanks for this
    <img src=/S/clever.gif border=0 alt=clever width=15 height=15><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  10. #10
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Maco Help (95/97/2000)

    Hey, this looks interesting. John's solution works a treat, but I am going to experiment with your solution on a copy of the workbook as part of my learning <img src=/S/confused.gif border=0 alt=confused width=15 height=20> curve
    <img src=/S/cool.gif border=0 alt=cool width=15 height=15><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Maco Help (95/97/2000)

    DoryO's version hides all the rows with no activity (time on the job) and then prints one page. My version prints the whole thing including no activity rows, and then prints each person with activity on a separate sheet.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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