Print only value

Question

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

all sorted.. thx all.. I just added excel function into the header => =IF(A3>0, "Stock","") and I did it for every 6 next column and then this code =>

 Dim ws As Worksheet
 Dim col As Long
 Dim cellValueRow2 As Variant
 Dim cellValueRow3 As Variant
 Dim response As VbMsgBoxResult
 Dim lastValidCol As Long
 Dim pagesToPrint As Long
 Dim userName As String
 
 ' Get Excel username
 userName = Application.userName
 
 ' Personalized greeting
 MsgBox "Hello " & userName & "! Let's print your pages.", vbInformation, "Print Assistant"
 
 ' Set to specific sheet
 On Error Resume Next
 Set ws = ThisWorkbook.Worksheets("Sheet1")
 On Error GoTo 0
 
 If ws Is Nothing Then
 MsgBox "Sheet1 not found.", vbExclamation
 Exit Sub
 End If
 
 lastValidCol = 0
 
 ' Check every 6th column starting at A (columns 1, 7, 13, etc.)
 For col = 1 To ws.Columns.Count Step 6
 cellValueRow2 = ws.Cells(2, col).Value
 cellValueRow3 = ws.Cells(3, col).Value
 
 ' Stop if row 2 is empty (stock)
 If Trim(CStr(cellValueRow2)) = "" Then
 Exit For
 End If
 
 ' Stop if row 3 has #N/A error
 If IsError(cellValueRow3) Then
 Exit For
 End If
 
 ' Stop if row 3 is empty
 If Trim(CStr(cellValueRow3)) = "" Then
 Exit For
 End If
 
 ' Stop if row 3 is 0
 If cellValueRow3 = 0 Then
 Exit For
 End If
 
 ' If we got here, this column has valid data
 lastValidCol = col + 5 ' Include the next 5 columns (full 6-column set)
 
 Next col
 
 If lastValidCol = 0 Then
 MsgBox "No data found to print. Check rows 2-3 on sheet1.", vbExclamation
 Exit Sub
 End If
 
 ' Set print area to only the valid columns
 ws.PageSetup.PrintArea = ws.Range(ws.Cells(1, 1), ws.Cells(ws.UsedRange.Rows.Count, lastValidCol)).Address
 
 ' Count actual pages after setting print area
 pagesToPrint = ws.PageSetup.Pages.Count
 
 response = MsgBox( _
 "This will print " & pagesToPrint & " page(s) from sheet1." & vbCrLf & vbCrLf & _
 "Do you want to continue?", _
 vbYesNo + vbQuestion, _
 "Print Confirmation")
 If response = vbYes Then
 ws.PrintOut
 ' Personalized success message
 MsgBox "Well done, " & userName & "! Your " & pagesToPrint & " page(s) have been sent to the printer.", vbInformation, "Print Complete"
 Else
 MsgBox "Printing cancelled.", vbInformation
 End If
End Sub

• Paul: 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?
• BruceWayne: 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)?
• Miki: @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 ..
• Shrotter: @Miki so you are searching the last "going"-column with a value other than zero?
• Miki: @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.

Answer 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.

• Miki: it is giving to you print preview
• Miki: but it is sorted thank you all..
• MGonet: You can comment out PrintPreview and uncomment PrintOut.
• Miki: but PintOut is giving you to print 195 pages..
• Miki: it is sorted you can see my updated code upper

Answer 2

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.

• Craig: 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?
• Community: 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.
• Miki: 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.
• Miki: 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.