Member Avatar for JVAII

Hello everybody,

I'm doing a colleague a favor by looking into a VBA formula to make her reporting tasks a little easier, poor her, but I need help myself now.

this is how her tabsheet is set up:

CVDV__tab.jpg

this is how the end result should look like in the printer tray:

CVDV_00AZ.jpg

CVDV_00AR.jpg

some facts:
- the values in column B ("APE", "APF", ...) are the 'seperators'. I tried to define a loop where the module selects the data until it finds an empty row, but there are 2 empty rows for each value in column B (e.g. for APE = row 7 & row 11) and it got me confused.
- the number of rows that need to be printed on a seperate page are dynamic: e.g. for B5 = "APE", one day it's 2 persons, the other day it's 15.
- the title in cell D2 should show on each page
- the number of columns doesn't change

Could use the help, thanks in advance!

Edited by JVAII
  • 3 Contributors
  • 2 Replies
  • 440 Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by ddanbe

Dani AI

Generated

This is a reliable, low-friction approach that avoids “stop at blank row” problems: treat each non-empty cell in column B as a separator/header, print the block of rows below it up to (but not including) the next non-empty B, and repeat the cell D2 on every printed page with PageSetup. As suggested, it helps if the data for printing is on a single sheet; as noted, use PageSetup for headers/print titles — the sample below uses PrintTitleRows to repeat row 2 (where D2 lives).

Sub PrintBlocksByColumnB()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, startRow As Long, endRow As Long, nextSep As Long

    Set ws = ActiveSheet          ' change to Worksheets("YourSheet") if needed
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    lastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column  ' assumes fixed columns

    ws.PageSetup.PrintTitleRows = "$2:$2"   ' repeat row 2 (D2) on every printed page

    r = 1
    Do While r <= lastRow
        If Len(Trim(ws.Cells(r, "B").Value)) > 0 Then
            startRow = r + 1
            nextSep = startRow
            Do While nextSep <= lastRow And Len(Trim(ws.Cells(nextSep, "B").Value)) = 0
                nextSep = nextSep + 1
            Loop
            endRow = nextSep - 1
            If endRow >= startRow Then
                ws.PageSetup.PrintArea = ws.Range(ws.Cells(startRow, 1), ws.Cells(endRow, lastCol)).Address
                ws.PrintOut Copies:=1, Preview:=True   ' Preview for testing; remove Preview in real runs
            End If
            r = nextSep
        Else
            r = r + 1
        End If
    Loop
End Sub

Notes and quick tips

  • If you want the text from D2 in the header instead of repeating the row, use ws.PageSetup.CenterHeader = ws.Range("D2").Value.
  • If identical separators (e.g., two separate "APE" blocks) should be merged into one print job, collect rows keyed by the B value (Dictionary) and copy them to a temp sheet, then print that sheet.
  • Test with Preview:=True first, and verify lastCol picks up the correct number of columns. If you need a VB.NET (Interop) version, the same logic applies and that can be supplied on request.

Recommended Answers

All 2 Replies

Member Avatar for DenisOxon

Are you using Excel ?

if so then write your results to one sheet. Then put a page heading in, via Page layout, then hit print button. You can scale it to get more on a page, etc.

Member Avatar for ddanbe

To print footers or headers on each printpage in Excel read this article.
How to use page breaks, you could read this
You specified vb.net if so, we like to see some code and were in the code it doesn't work.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.