Print Area Set for Columns but Not Rows

S

Swingdancer42

Help,

I thought this would be a redundant question, but I can't seem to find the
answer in any of the previous posts. I have a spreadsheet with Columns A:R,
but I only want columns A:N to print. I also want Excel to automatically
print only up to the last row with data entered. Looking through all the
previous posts, I thought this would do the trick:

Sub PrepareSheet(sht As Worksheet)
Dim lRow As Long
lRow = sht.Range("A:N").SpecialCells(xlCellTypeLastCell).Row
With sht.PageSetup
.PrintArea = "$A$1:$N" & lRow
.Zoom = False
.FitToPagesWide = 1
End With
End Sub

Unfortunately this is only accomplishing half of my desires - while this
DOES only print the rows with inputs, it's printing all my columns (instead
of just columns A to N). Can anybody help me? What's the code I should be
using?

Thanks in advance for any help you can provide!!!
 
S

Simon Lloyd

This should do what you want
Code
-------------------
Sub PrepareSheet(
Dim lRow As Lon
lRow = ActiveSheet.Range("N" & Rows.Count).End(xlUp).Ro
With ActiveSheet.PageSetu
.PrintArea = "$A$1:$N$" & lRo
.Zoom = Fals
.FitToPagesWide =
End Wit
End Su
-------------------

Swingdancer42;216398 said:
Help

I thought this would be a redundant question, but I can't seem to fin
th
answer in any of the previous posts. I have a spreadsheet with Column
A:R
but I only want columns A:N to print. I also want Excel t
automaticall
print only up to the last row with data entered. Looking through al
th
previous posts, I thought this would do the trick

Sub PrepareSheet(sht As Worksheet
Dim lRow As Lon
lRow = sht.Range("A:N").SpecialCells(xlCellTypeLastCell).Ro
With sht.PageSetu
.PrintArea = "$A$1:$N" & lRo
.Zoom = Fals
.FitToPagesWide =
End Wit
End Su

Unfortunately this is only accomplishing half of my desires - whil
thi
DOES only print the rows with inputs, it's printing all my column
(instea
of just columns A to N). Can anybody help me? What's the code I shoul
b
using

Thanks in advance for any help you can provide!!
Click to expand...

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
S

Swingdancer42

Hi Simon,

Thanks for helping! Unfortunately it didn't work, I replaced the code I
posted with the code you provided and it is still producing the same results
- it's still printing all my columns (A:R) but I only want it to print A:N.

Any more ideas? Your help is much appreciated!
 
S

Simon Lloyd

Swingdancer, it would be good if you could attach the workbook, you ca
join the forum in the link below for free, when making a post before yo
click the submit button scroll down and you will see Manage Attachment
this is where you can upload a sample workbook, if you do join the
please make your post in this threa
'http://www.thecodecage.com/forumz/showthread.php?t=59441
(http://www.thecodecage.com/forumz/showthread.php?t=59441) so tha
people who have been following or helping can continue to do so

Swingdancer42;217306 said:
Hi Simon

Thanks for helping! Unfortunately it didn't work, I replaced the code
posted with the code you provided and it is still producing the sam
result
- it's still printing all my columns (A:R) but I only want it to prin
A:N

Any more ideas? Your help is much appreciated




Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1
Cage Forums' (http://www.thecodecage.com/forumz/showthread.php?t=59441
Click to expand...

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
S

Swingdancer42

Hi Simon,

Thanks again for your help! Attached is one of my worksheets. I ha
to delete all the drop-down box information(s) but that shouldn't affec
the question.
Can you help?

Thanks!
Elizabet

+-------------------------------------------------------------------
|Filename: FY10 Spend Plan Template for Review.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=73
+-------------------------------------------------------------------
 
Top