Bland Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
You can create a dynamic range named Print_Area, as described here:

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)
 
Right click excel icon in upper left next to file>view code>insert
this>save>test

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
 
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.
 
And you may want to make it a sheet level name:

Insert|Name|Define
Names in workbook box: 'Sheet1'!Print_Area
 
I got a syntax error on this line of the code ..PageSetup.PrintArea =
..Rows("1:" & mr).Address
 
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
(e-mail address removed)
 
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 said:
And you may want to make it a sheet level name:

Insert|Name|Define
Names in workbook box: 'Sheet1'!Print_Area
Click to expand...
 
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
 
Back
Top