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.--
smplogc
------------------------------------------------------------------------
smplogc's Profile: www.excelforum.com/member.php...oamp;userid=33887
View this thread: www.excelforum.com/showthread...hreadid=537072
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 gt; Print Area gt; 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)--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=537072
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. --
smplogc
------------------------------------------------------------------------
smplogc's Profile: www.excelforum.com/member.php...oamp;userid=33887
View this thread: www.excelforum.com/showthread...hreadid=537072You 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.
quot;smplogcquot; gt; wrote in
message ...
gt;
gt; Thanks for the reply.
gt;
gt; The problem is that my spreadsheet will be used by very
gt; computer-illiterate people, and I'd like to make tasks as simple and
gt; fool-proof as possible.
gt;
gt;
gt; --
gt; smplogc
gt; ------------------------------------------------------------------------
gt; smplogc's Profile:
www.excelforum.com/member.php...oamp;userid=33887
gt; View this thread: www.excelforum.com/showthread...hreadid=537072
gt;
PY amp; Associates Wrote:
gt; You must know how many lines your print page holds;
gt; You must also be able to count the rows one way or another;
gt; 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.--
smplogc
------------------------------------------------------------------------
smplogc's Profile: www.excelforum.com/member.php...oamp;userid=33887
View this thread: www.excelforum.com/showthread...hreadid=537072Hehe, I say quot;tipsquot;, but you guys do all the work!
quot;smplogcquot; gt; wrote in
message ...
gt;
gt; PY amp; Associates Wrote:
gt; gt; You must know how many lines your print page holds;
gt; gt; You must also be able to count the rows one way or another;
gt; gt; then you can do what you want.
gt;
gt; Hehe, yup, but I don't know how to implement that in VB code or
gt; whatever method would do the trick.
gt;
gt;
gt; --
gt; smplogc
gt; ------------------------------------------------------------------------
gt; smplogc's Profile:
www.excelforum.com/member.php...oamp;userid=33887
gt; View this thread: www.excelforum.com/showthread...hreadid=537072
gt;
This is the best solution I could find on my own. It's not my code, of
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(quot;Sheet1quot;)
For rw = 33 To 111
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range(quot;A1:F1quot;)) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range(quot;A33:A111quot;).EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub- rows 1-11 are header text and column headings that appear on every
page
- rows 12-32 are the minimum number of data rows that appear on the
first page
- rows 33-111 are additional rows that will only print if they contain
any data
- rows 112-115 will always print underneath whatever number of data
rows are printed (from 21 to 100)
My ragtag abomination of a spreadsheet is coming to life!--
smplogc
------------------------------------------------------------------------
smplogc's Profile: www.excelforum.com/member.php...oamp;userid=33887
View this thread: www.excelforum.com/showthread...hreadid=537072
This is the best solution I could find on my own. It's not my code, of
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(quot;Sheet1quot;)
For rw = 33 To 111
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range(quot;A1:F1quot;)) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range(quot;A33:A111quot;).EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub- rows 1-11 are header text and column headings that appear on every
page
- rows 12-32 are the minimum number of data rows that appear on the
first page
- rows 33-111 are additional rows that will only print if they contain
any data
- rows 112-115 will always print underneath whatever number of data
rows are printed (from 21 to 100)
My ragtag abomination of a spreadsheet is coming to life!--
smplogc
------------------------------------------------------------------------
smplogc's Profile: www.excelforum.com/member.php...oamp;userid=33887
View this thread: www.excelforum.com/showthread...hreadid=537072For 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(quot;56:199quot;).Delete
If p = 1 Then Rows(quot;112:199quot;).Delete
If p = 2 Then Rows(quot;168:199quot;).Delete
If p = 3 Then
b = b 169
Rows(b amp; quot;:199quot;).Delete
Rows(b amp; quot;:223quot;).Insert
End If
reset printrange before print
quot;smplogcquot; gt; wrote in
message ...
gt;
gt; This is the best solution I could find on my own. It's not my code, of
gt; course - I don't know VB, but I'm starting to understand it.
gt;
gt; Sub Hide_Print_Unhide()
gt; Dim rw As Long
gt; Application.ScreenUpdating = False
gt;
gt; With Sheets(quot;Sheet1quot;)
gt; For rw = 33 To 111
gt; If Application.WorksheetFunction.CountA( _
gt; Cells(rw, 1).Range(quot;A1:F1quot;)) = 0 Then _
gt; Rows(rw).Hidden = True
gt; Next rw
gt; PrintOut ' for testing use .PrintPreview
gt; Range(quot;A33:A111quot;).EntireRow.Hidden = False
gt; End With
gt;
gt; Application.ScreenUpdating = True
gt; End Sub
gt;
gt;
gt; - rows 1-11 are header text and column headings that appear on every
gt; page
gt; - rows 12-32 are the minimum number of data rows that appear on the
gt; first page
gt; - rows 33-111 are additional rows that will only print if they contain
gt; any data
gt; - rows 112-115 will always print underneath whatever number of data
gt; rows are printed (from 21 to 100)
gt;
gt; My ragtag abomination of a spreadsheet is coming to life!
gt;
gt;
gt; --
gt; smplogc
gt; ------------------------------------------------------------------------
gt; smplogc's Profile:
www.excelforum.com/member.php...oamp;userid=33887
gt; View this thread: www.excelforum.com/showthread...hreadid=537072
gt;
PY amp; Associates Wrote:
gt; For your reference. Tested on screen, OK
I'll experiment with your code. Thanks a lot.--
smplogc
------------------------------------------------------------------------
smplogc's Profile: www.excelforum.com/member.php...oamp;userid=33887
View this thread: www.excelforum.com/showthread...hreadid=537072
- Jul 25 Fri 2008 20:45
Automated Dynamic Printing Area?
close
全站熱搜
留言列表
發表留言