Bland Rows
 

My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered.
All of the columns are used if a row is used. My sheet has 15% more rows
than our highest use this year so that I am sure our office does not run
short of rows (my workbook has 12 of these sheets because we track the info
monthly and forward the sheets as filled out to other personel).

My question is how do I print the sheets (only one month is printed at a
time) to print only the rows that has been filled out, all of the sheet rows
are continuous, we have no unused rows except for the ones left at the bottom
that are unused. when ever I print the sheets it always prints a few extra
pages from the bottom that are just the gridding. I don't want to have to
delete all of the unused row before I print the sheet.

Bland Rows
 

Sub rhide()
Dim nLastRow As Long
With ActiveSheet.UsedRange
nLastRow = .Rows.Count + .Row - 1
End With
For i = 1 To nLastRow
If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Hidden = True
End If
Next

Set r = Range(Cells(nLastRow + 1, "A"), Cells(65536, "A"))
r.EntireRow.Hidden = True
End Sub


This macro will hide any empty rows within your data. It will also hide any
blank rows outside of your data. Just run it prior to printing.
--
Gary's Student


"tankerman" wrote:

My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered.
All of the columns are used if a row is used. My sheet has 15% more rows
than our highest use this year so that I am sure our office does not run
short of rows (my workbook has 12 of these sheets because we track the info
monthly and forward the sheets as filled out to other personel).

My question is how do I print the sheets (only one month is printed at a
time) to print only the rows that has been filled out, all of the sheet rows
are continuous, we have no unused rows except for the ones left at the bottom
that are unused. when ever I print the sheets it always prints a few extra
pages from the bottom that are just the gridding. I don't want to have to
delete all of the unused row before I print the sheet.

Bland Rows
 

You can create a dynamic range named Print_Area, as described he

http://www.contextures.com/xlNames01.html

In the Offset formula, you can count the cells that contain text. For
example, if column A will always have an entry if the row is used:

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A$1:$A$250 ,"<"),33)

tankerman wrote:
My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered.
All of the columns are used if a row is used. My sheet has 15% more rows
than our highest use this year so that I am sure our office does not run
short of rows (my workbook has 12 of these sheets because we track the info
monthly and forward the sheets as filled out to other personel).

My question is how do I print the sheets (only one month is printed at a
time) to print only the rows that has been filled out, all of the sheet rows
are continuous, we have no unused rows except for the ones left at the bottom
that are unused. when ever I print the sheets it always prints a few extra
pages from the bottom that are just the gridding. I don't want to have to
delete all of the unused row before I print the sheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Bland Rows
 

Right click excel icon in upper left next to fileview codeinsert
thissavetest

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
lr = .Cells(.Rows.Count, "a").End(xlUp).Row
For r = lr To 1 Step -1
If Application.CountA(.Rows(r)) 1 Then mr = .Rows(r).Row: Exit For
Next r
..PageSetup.PrintArea = .Rows("1:" & mr).Address
'or to limit columns
'.PageSetup.PrintArea = .Range("a2:x" & mr).Address
End With
End Sub

--
Don Guillett
SalesAid Software

"tankerman" wrote in message
...
My spreadsheet has 250 rows and A thru AG in columns, all cells are
bordered.
All of the columns are used if a row is used. My sheet has 15% more rows
than our highest use this year so that I am sure our office does not run
short of rows (my workbook has 12 of these sheets because we track the
info
monthly and forward the sheets as filled out to other personel).

My question is how do I print the sheets (only one month is printed at a
time) to print only the rows that has been filled out, all of the sheet
rows
are continuous, we have no unused rows except for the ones left at the
bottom
that are unused. when ever I print the sheets it always prints a few
extra
pages from the bottom that are just the gridding. I don't want to have to
delete all of the unused row before I print the sheet.

Bland Rows
 

Just to add:
You just need to be aware that if you do File=Page Setup (no other action
necessary), it removes your formula for that page and replaces it with a
hard coded range. so avoid doing page setup.

--
Regards,
Tom Ogilvy


"Debra Dalgleish" wrote in message
...
You can create a dynamic range named Print_Area, as described he

http://www.contextures.com/xlNames01.html

In the Offset formula, you can count the cells that contain text. For
example, if column A will always have an entry if the row is used:

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A$1:$A$250 ,"<"),33)

tankerman wrote:
My spreadsheet has 250 rows and A thru AG in columns, all cells are
bordered. All of the columns are used if a row is used. My sheet has 15%
more rows than our highest use this year so that I am sure our office
does not run short of rows (my workbook has 12 of these sheets because we
track the info monthly and forward the sheets as filled out to other
personel). My question is how do I print the sheets (only one month is
printed at a time) to print only the rows that has been filled out, all
of the sheet rows are continuous, we have no unused rows except for the
ones left at the bottom that are unused. when ever I print the sheets it
always prints a few extra pages from the bottom that are just the
gridding. I don't want to have to delete all of the unused row before I
print the sheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Bland Rows
 

And you may want to make it a sheet level name:

Insert|Name|Define
Names in workbook box: 'Sheet1'!Print_Area



Debra Dalgleish wrote:

You can create a dynamic range named Print_Area, as described he

http://www.contextures.com/xlNames01.html

In the Offset formula, you can count the cells that contain text. For
example, if column A will always have an entry if the row is used:

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A$1:$A$250 ,"<"),33)

tankerman wrote:
My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered.
All of the columns are used if a row is used. My sheet has 15% more rows
than our highest use this year so that I am sure our office does not run
short of rows (my workbook has 12 of these sheets because we track the info
monthly and forward the sheets as filled out to other personel).

My question is how do I print the sheets (only one month is printed at a
time) to print only the rows that has been filled out, all of the sheet rows
are continuous, we have no unused rows except for the ones left at the bottom
that are unused. when ever I print the sheets it always prints a few extra
pages from the bottom that are just the gridding. I don't want to have to
delete all of the unused row before I print the sheet.

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

--

Dave Peterson

Bland Rows
 

I got a syntax error on this line of the code ..PageSetup.PrintArea =
..Rows("1:" & mr).Address

"Don Guillett" wrote:

Right click excel icon in upper left next to fileview codeinsert
thissavetest

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
lr = .Cells(.Rows.Count, "a").End(xlUp).Row
For r = lr To 1 Step -1
If Application.CountA(.Rows(r)) 1 Then mr = .Rows(r).Row: Exit For
Next r
..PageSetup.PrintArea = .Rows("1:" & mr).Address
'or to limit columns
'.PageSetup.PrintArea = .Range("a2:x" & mr).Address
End With
End Sub

--
Don Guillett
SalesAid Software

"tankerman" wrote in message
...
My spreadsheet has 250 rows and A thru AG in columns, all cells are
bordered.
All of the columns are used if a row is used. My sheet has 15% more rows
than our highest use this year so that I am sure our office does not run
short of rows (my workbook has 12 of these sheets because we track the
info
monthly and forward the sheets as filled out to other personel).

My question is how do I print the sheets (only one month is printed at a
time) to print only the rows that has been filled out, all of the sheet
rows
are continuous, we have no unused rows except for the ones left at the
bottom
that are unused. when ever I print the sheets it always prints a few
extra
pages from the bottom that are just the gridding. I don't want to have to
delete all of the unused row before I print the sheet.

Bland Rows
 

I have tried to keep whatever is doing it NOT put in the additional dot.
..Rows("1:" & mr).Address
should be
.Rows("1:" & mr).Address

Don Guillett
SalesAid Software

"tankerman" wrote in message
...
I got a syntax error on this line of the code ..PageSetup.PrintArea =
.Rows("1:" & mr).Address

"Don Guillett" wrote:

Right click excel icon in upper left next to fileview codeinsert
thissavetest

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
lr = .Cells(.Rows.Count, "a").End(xlUp).Row
For r = lr To 1 Step -1
If Application.CountA(.Rows(r)) 1 Then mr = .Rows(r).Row: Exit For
Next r
..PageSetup.PrintArea = .Rows("1:" & mr).Address
'or to limit columns
'.PageSetup.PrintArea = .Range("a2:x" & mr).Address
End With
End Sub

--
Don Guillett
SalesAid Software

"tankerman" wrote in message
...
My spreadsheet has 250 rows and A thru AG in columns, all cells are
bordered.
All of the columns are used if a row is used. My sheet has 15% more
rows
than our highest use this year so that I am sure our office does not
run
short of rows (my workbook has 12 of these sheets because we track the
info
monthly and forward the sheets as filled out to other personel).

My question is how do I print the sheets (only one month is printed at
a
time) to print only the rows that has been filled out, all of the sheet
rows
are continuous, we have no unused rows except for the ones left at the
bottom
that are unused. when ever I print the sheets it always prints a few
extra
pages from the bottom that are just the gridding. I don't want to have
to
delete all of the unused row before I print the sheet.

Bland Rows
 

Sorry, I forgot to mention that a sheet level name is required.

And thanks Tom, I'd never noticed that using Page Setup removes the
dynamic formula.

Dave Peterson wrote:
And you may want to make it a sheet level name:

Insert|Name|Define
Names in workbook box: 'Sheet1'!Print_Area



Debra Dalgleish wrote:

You can create a dynamic range named Print_Area, as described he

http://www.contextures.com/xlNames01.html

In the Offset formula, you can count the cells that contain text. For
example, if column A will always have an entry if the row is used:

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A$1:$A$250 ,"<"),33)

tankerman wrote:

My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered.
All of the columns are used if a row is used. My sheet has 15% more rows
than our highest use this year so that I am sure our office does not run
short of rows (my workbook has 12 of these sheets because we track the info
monthly and forward the sheets as filled out to other personel).

My question is how do I print the sheets (only one month is printed at a
time) to print only the rows that has been filled out, all of the sheet rows
are continuous, we have no unused rows except for the ones left at the bottom
that are unused. when ever I print the sheets it always prints a few extra
pages from the bottom that are just the gridding. I don't want to have to
delete all of the unused row before I print the sheet.

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Bland Rows
 

Don,
I was having the same issues and used your code. It works great except it
prints one extra blank page. Any suggestions. Is there something I need to
change in the code to prevent this from happening.

Thanks,
Mickey

"Don Guillett" wrote:

Right click excel icon in upper left next to fileview codeinsert
thissavetest

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
lr = .Cells(.Rows.Count, "a").End(xlUp).Row
For r = lr To 1 Step -1
If Application.CountA(.Rows(r)) 1 Then mr = .Rows(r).Row: Exit For
Next r
..PageSetup.PrintArea = .Rows("1:" & mr).Address
'or to limit columns
'.PageSetup.PrintArea = .Range("a2:x" & mr).Address
End With
End Sub

--
Don Guillett
SalesAid Software

"tankerman" wrote in message
...
My spreadsheet has 250 rows and A thru AG in columns, all cells are
bordered.
All of the columns are used if a row is used. My sheet has 15% more rows
than our highest use this year so that I am sure our office does not run
short of rows (my workbook has 12 of these sheets because we track the
info
monthly and forward the sheets as filled out to other personel).

My question is how do I print the sheets (only one month is printed at a
time) to print only the rows that has been filled out, all of the sheet
rows
are continuous, we have no unused rows except for the ones left at the
bottom
that are unused. when ever I print the sheets it always prints a few
extra
pages from the bottom that are just the gridding. I don't want to have to
delete all of the unused row before I print the sheet.