Print non blanks

[krlouvae]

This is my first thread over here, I hope I'll have some luck with it


I have a sheet, with 132 pages (all with page breaks). Not all those pages have values. So, some of these pages are just blank.
I need a code that activates the sheet, selects all the non blanks, and prints them out.
The blanks are actually "" and the non blanks have values through formula's ... (don't know if that mathers).
All of my pages are in COL A (A1:A132). In the next table, code should select A3, A5 and A6, and print those out

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD] COL A[/TD]
[/TR]
[TR]
[TD]ROW 1[/TD]
[TD]=""[/TD]
[/TR]
[TR]
[TD]ROW 2[/TD]
[TD]=""[/TD]
[/TR]
[TR]
[TD]ROW 3[/TD]
[TD]TEST[/TD]
[/TR]
[TR]
[TD]ROW 4[/TD]
[TD]=""[/TD]
[/TR]
[TR]
[TD]ROW 5[/TD]
[TD]TEST II[/TD]
[/TR]
[TR]
[TD]ROW 6[/TD]
[TD]TEST III[/TD]
[/TR]
[TR]
[TD]ROW 7[/TD]
[TD]=""[/TD]
[/TR]
</tbody>[/TABLE]


Can someone help me out on this one please?
Thx !!

 

[Peter_SSs]

Welcome to the MrExcel board!

When you say you have 132 pages, do your mean

- you have one worksheet with so much data it takes 132 pages to print, or

- you have a workbook with 132 worksheets that each need printing, or

- something else?

 

[krlouvae]

Welcome to the MrExcel board!

When you say you have 132 pages, do your mean

- you have one worksheet with so much data it takes 132 pages to print, or

- you have a workbook with 132 worksheets that each need printing, or

- something else?

Thx for the welcome

That's 132 pages in one worksheet. And it's not that much because it's to much data,
it's that much because i have 132 values that each take 1 full page to print.
It's each time a numer (eg 8500) or multiple numbers (8500 - 8510) that are stretched out
on each page.

 

[Peter_SSs]

It's each time a numer (eg 8500) or multiple numbers (8500 - 8510) that are stretched out

Not sure how/if that relates to that sample data that seems to have text not numerical entries but in any case, do you really need code?
Couldn't you just use AutoFilter to filter for non-blanks and Print?
Is there a heading above the data?

 

[krlouvae]

Well it doesn't mather what the values are, some are texts, some are numbers, I just need the non blanks to be printed.
And I would love to see that with a code, because it's a file that's used by people that need those non blanks really quick and they don't have the time or knowledge to use autofilters.
No headings above ...

If it isn't possible, I will need to make headings and work with autofilters, but I thought that this question would be an easy one for this forum.

 

[Peter_SSs]

Somebody may have a better idea of this than me but I think your existing page breaks (at least horizontal page breaks) will cause a problem.
My suggestion would be to (in a copy of your workbook ) remove all the horizontal page breaks then try this code.
For testing I would also ensure most of the cells in column A are blank since my code does a PrintPreview of each non-blank row and you will have to close each PrintPreview for the code to move forward.
If satisfied that it does the job then change the .PrintPreview in the code to .Printout

Sub Print_Excluding_Blanks()
  Dim Cell As Range, rColA As Range
  
  Application.ScreenUpdating = False
  Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
  rColA.EntireRow.Hidden = True
  For Each Cell In rColA
    If Cell.Value <> "" Then
      Cell.EntireRow.Hidden = False
      ActiveSheet.PrintPreview '.PrintOut
      Cell.EntireRow.Hidden = True
    End If
  Next Cell
  rColA.EntireRow.Hidden = False
  Application.ScreenUpdating = True
End Sub

 

[krlouvae]

Somebody may have a better idea of this than me but I think your existing page breaks (at least horizontal page breaks) will cause a problem.
My suggestion would be to (in a copy of your workbook ) remove all the horizontal page breaks then try this code.
For testing I would also ensure most of the cells in column A are blank since my code does a PrintPreview of each non-blank row and you will have to close each PrintPreview for the code to move forward.
If satisfied that it does the job then change the .PrintPreview in the code to .Printout

Sub Print_Excluding_Blanks()
  Dim Cell As Range, rColA As Range
  
  Application.ScreenUpdating = False
  Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
  rColA.EntireRow.Hidden = True
  For Each Cell In rColA
    If Cell.Value <> "" Then
      Cell.EntireRow.Hidden = False
      ActiveSheet.PrintPreview '.PrintOut
      Cell.EntireRow.Hidden = True
    End If
  Next Cell
  rColA.EntireRow.Hidden = False
  Application.ScreenUpdating = True
End Sub

That worked just fine, even without removing page breaks. Thanks a lot !!!

 

[Peter_SSs]

That worked just fine, even without removing page breaks. Thanks a lot !!!

That's good. Thanks for letting us know.