Automated Dynamic Printing Area?

[smplogc]

Is this possible?

Say you have a 200 row spreadsheet with various functions being
performed in the bottom cells. If only the first few rows are filled
with data, when printed, all the empty rows that won't fit on on the
first page will automatically be omitted, except for those important
bottom rows. So the first page will have the rows filled with data,
plus empty rows to fill out the page, and the bottom rows.

If there are one too many filled rows to fit on one page, then a second
page will be printed with enough empty rows to fill the page, with the
bottom rows included at the bottom of the second page.

In other words, I want print jobs to be scaled in terms of pages based
on filled rows, with important bottom rows automatically added at the
end of the last page of the job.

 

[starguy]

you can do this by hiding empty rows in between your data rows. you can
do this very quickly by using commands.

first select your data range which you want to print (from top to
bottom).
then File > Print Area > Set Print Area

now when you want to take print Just apply following commands.
click on cell in row next to your entered data.
press Ctrl+Shift+Down Arrow Key (it will select all blank rows to last
row which has functions).
Now hold only Shift key and press Up Arrow. It will deselect the row
which has data (Functions).
Now press Ctrl+9 (nine below F8 not on numeric key pad), it will hide
your empty rows.
and at last press Ctrl+P to print the document.

It seems long process but it is't so. It take just 3 to 5 Seconds to
take a print. (because I used to do this on regular basis)

 

[smplogc]

Thanks for the reply.

The problem is that my spreadsheet will be used by very
computer-illiterate people, and I'd like to make tasks as simple and
fool-proof as possible.

 

[PY & Associates]

You must know how many lines your print page holds;
You must also be able to count the rows one way or another;
then you can do what you want.

 

[smplogc]

You must know how many lines your print page holds;
You must also be able to count the rows one way or another;
then you can do what you want.

Hehe, yup, but I don't know how to implement that in VB code or
whatever method would do the trick.

 

[PY & Associates]

Hehe, I say "tips", but you guys do all the work!

 

[smplogc]

This is the best solution I could find on my own. It's not my code, o
course - I don't know VB, but I'm starting to understand it.

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Sheet1")
For rw = 33 To 111
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A1:F1")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A33:A111").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


- rows 1-11 are header text and column headings that appear on ever
page
- rows 12-32 are the minimum number of data rows that appear on th
first page
- rows 33-111 are additional rows that will only print if they contai
any data
- rows 112-115 will always print underneath whatever number of dat
rows are printed (from 21 to 100)

My ragtag abomination of a spreadsheet is coming to life

 

[smplogc]

This is the best solution I could find on my own. It's not my code, o
course - I don't know VB, but I'm starting to understand it.

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Sheet1")
For rw = 33 To 111
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A1:F1")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A33:A111").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


- rows 1-11 are header text and column headings that appear on ever
page
- rows 12-32 are the minimum number of data rows that appear on th
first page
- rows 33-111 are additional rows that will only print if they contai
any data
- rows 112-115 will always print underneath whatever number of dat
rows are printed (from 21 to 100)

My ragtag abomination of a spreadsheet is coming to life

 

[PY & Associates]

For your reference. Tested on screen, OK

copy data to temp sheet

rownr = Cells(1, 1).End(xlDown).Row
p = Int(rownr / 56)
b = rownr Mod 56

If p = 0 Then Rows("56:199").Delete
If p = 1 Then Rows("112:199").Delete
If p = 2 Then Rows("168:199").Delete
If p = 3 Then
b = b + 169
Rows(b & ":199").Delete
Rows(b & ":223").Insert
End If

reset printrange before print

 

[smplogc]

For your reference. Tested on screen, OK

I'll experiment with your code. Thanks a lot.