0

I have a table where I insert data and I want to print it. The table is set to 195 pages because I have to cover the entire area I need, but I want control a certain area, not the whole area, so let's say the data is only filled and can be printed on 5 pages. I have the code on VBA = >

Sub printValue()

ActiveSheet.PageSetup.PrintArea = Range("A2:CLQ41", Range("A2:CLQ41").End(xlDown)).Address

ActiveWindow.SelectedSheets.PrintOut

End Sub

but this code is print still all 195 pages, and I want it to print only the pages that contain some info in the table and ignore 0's.I can do them blank is not problem, but the table also has a header so it still go for full 195 pages, see the image below. Is this possible via VBA? Anyone can help?

TABLE 1

red count

Improve this question
5
  • First, you'll have to be a bit clearer about what you want to print. Is the sample you show "data" (even if the issue column is not filled in)? What do the parts without data look like? Commented yesterday
  • What happens if your xlDown ends up at the very last row, CLQ1048576? I'd add an error handler, or do xlUp from the last row (e.g. Range("A2:CLQ1048576").End(xlUp)? Commented yesterday
  • @BruceWayne nothing can happened on CLQ01048576. It is range A2 to CLQ41. The code is set like this because when do I press ctrl+p it is giving you margin for print and 1 classic A4 paper is range A1 to A41. xlDown is finish on 41 and then going back to up .. Commented 16 hours ago
  • @Miki so you are searching the last "going"-column with a value other than zero? Commented 16 hours ago
  • @Shrotter yep this way or the other way. I added picture with the red marks.. if there are data from the row3 to row 40 print with the header which is in row2. Commented 16 hours ago

2 Answers 2

Reset to default
1

If the worksheet structure is fixed (6-column data blocks that include the header row and data in rows 3:41), you can use the following code:

Sub SetPrintArea()
    Dim testcol As Long, fcol As Range, prncols As Range
    With ActiveSheet
        For testcol = .Columns("A").Column To .Columns("CLQ").Column Step 6
            Set fcol = Intersect(.Columns(testcol), .Rows("3:41"))
            If Application.CountIf(fcol, "<>") > 0 Then
                If prncols Is Nothing Then
                    Set prncols = fcol.Resize(, 6)
                Else
                    Set prncols = Union(prncols, fcol.Resize(, 6))
                End If
            End If
        Next testcol
        With .PageSetup
            .PrintArea = prncols.Address
            .PrintTitleRows = "$2:$2"
        End With
        .PrintPreview
      '   .PrintOut
    End With
End Sub

Every sixth column of data is checked, and if there are non-blank cells in it, the block is attached to the printout.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-1 
Sub PrintNonZeroArea()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim maxRow As Long, maxCol As Long
    
    Set ws = ActiveSheet
    
    maxRow = 1 ' Start with header row
    maxCol = 1
    
    ' Loop through all used cells in the table (adjust table range if needed)
    For r = 2 To ws.Rows.Count ' Start from row 2 if row 1 is header
        For c = 1 To ws.Columns.Count
            If IsNumeric(ws.Cells(r, c).Value) Then
                If ws.Cells(r, c).Value <> 0 Then
                    If r > maxRow Then maxRow = r
                    If c > maxCol Then maxCol = c
                End If
            ElseIf ws.Cells(r, c).Value <> "" Then
                ' Non-numeric data counts too
                If r > maxRow Then maxRow = r
                If c > maxCol Then maxCol = c
            End If
        Next c
    Next r
    
    ' Set print area including header
    ws.PageSetup.PrintArea = ws.Range(ws.Cells(1, 1), ws.Cells(maxRow, maxCol)).Address
    
    ' Print
    ws.PrintOut
End Sub

Hope this helps man. It could be wrong because I don't have any Sample data, I just created something similar.

Improve this answer
New contributor
Fulufhelo M is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

6 Comments

Can you add any discussion of what was wrong in the original code and/or the features in your code that help to answer the question?
As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
original code is reading all tables. So from A2 is table permanent to CLQ column (row2 to row40). The tables are blank, no data (only row2 is a header so is always there) Then I add some reports to different spreadsheets and the data is start filling as there is excel function set up in every column. Then I going print it off. When click the bottom @Print Me' is printing all tables from page 1 (a2) to page 195(CLQ) even there are not data. I want to print only header and the table if there are data in rows or column.
ignore the table where is 0 for example or print the tables if data are in A3, then G3, then M3 up to CLQ3, so every 6 column has to be with data then print only not all blank tables.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.