Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area
I need to set the print area for an ever changing range of rows (may start at
row 200 on one execution, may start at Row 300 on the next), but no wider than Column G. The length of the Print Area is also ever changing, therefore I have used a formula that finds the last occupied row. I made some changes to the .Range () and now it no longer finds the last occupied row (finds all BUT the last row). IF 8 rows are occupied, THEN output is 7 rows. Can anyone see what I'm doing wrong? Sub Print_Preview() Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim BldRngeTop 'First cell in Print Area Dim BldRngeWdth 'Last Column in Print Area BldRngeTop = Range("Extract_Area_FirstCell").Address BldRngeWdth = "G" Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row Set rng = .Range(BldRngeTop & ":" & BldRngeWdth & LRow) .PageSetup.PrintArea = rng.Address End With ActiveWindow.SelectedSheets.PrintPreview End Sub Thanks for any help you might give. -- FirstVette52 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area
How is "Extract_Area_FirstCell" defined. Is this a manually named cell?
Paul "FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote in message ... I need to set the print area for an ever changing range of rows (may start at row 200 on one execution, may start at Row 300 on the next), but no wider than Column G. The length of the Print Area is also ever changing, therefore I have used a formula that finds the last occupied row. I made some changes to the .Range () and now it no longer finds the last occupied row (finds all BUT the last row). IF 8 rows are occupied, THEN output is 7 rows. Can anyone see what I'm doing wrong? Sub Print_Preview() Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim BldRngeTop 'First cell in Print Area Dim BldRngeWdth 'Last Column in Print Area BldRngeTop = Range("Extract_Area_FirstCell").Address BldRngeWdth = "G" Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row Set rng = .Range(BldRngeTop & ":" & BldRngeWdth & LRow) .PageSetup.PrintArea = rng.Address End With ActiveWindow.SelectedSheets.PrintPreview End Sub Thanks for any help you might give. -- FirstVette52 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area
Yes it is (named, 1 cell range)
Thx! -- FirstVette52 "Paul" wrote: How is "Extract_Area_FirstCell" defined. Is this a manually named cell? Paul "FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote in message ... I need to set the print area for an ever changing range of rows (may start at row 200 on one execution, may start at Row 300 on the next), but no wider than Column G. The length of the Print Area is also ever changing, therefore I have used a formula that finds the last occupied row. I made some changes to the .Range () and now it no longer finds the last occupied row (finds all BUT the last row). IF 8 rows are occupied, THEN output is 7 rows. Can anyone see what I'm doing wrong? Sub Print_Preview() Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim BldRngeTop 'First cell in Print Area Dim BldRngeWdth 'Last Column in Print Area BldRngeTop = Range("Extract_Area_FirstCell").Address BldRngeWdth = "G" Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row Set rng = .Range(BldRngeTop & ":" & BldRngeWdth & LRow) .PageSetup.PrintArea = rng.Address End With ActiveWindow.SelectedSheets.PrintPreview End Sub Thanks for any help you might give. -- FirstVette52 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area
I haven't tested but try replacing following line
LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row with this line LRow = .Cells(.Rows.Count, BldRngeWdth).End(xlUp).Row I have removed SH. Not needed when already using With SH. Placed . in front of Rows.Count because it belongs to SH If above does not work then place Stop after End With and then while stopped hover cursor over the various variables like LRow and rng.Address and check their values. -- Regards, OssieMac "FirstVette52" wrote: I need to set the print area for an ever changing range of rows (may start at row 200 on one execution, may start at Row 300 on the next), but no wider than Column G. The length of the Print Area is also ever changing, therefore I have used a formula that finds the last occupied row. I made some changes to the .Range () and now it no longer finds the last occupied row (finds all BUT the last row). IF 8 rows are occupied, THEN output is 7 rows. Can anyone see what I'm doing wrong? Sub Print_Preview() Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim BldRngeTop 'First cell in Print Area Dim BldRngeWdth 'Last Column in Print Area BldRngeTop = Range("Extract_Area_FirstCell").Address BldRngeWdth = "G" Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row Set rng = .Range(BldRngeTop & ":" & BldRngeWdth & LRow) .PageSetup.PrintArea = rng.Address End With ActiveWindow.SelectedSheets.PrintPreview End Sub Thanks for any help you might give. -- FirstVette52 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area
I decided to test your code because I thought that it should work,
particularly in view of the fact that you said it is returning one less row. Anyway your code works even though I still think that you should make my suggested changes . Therefore there is another problem. Please don't take offence at me for asking this but sometimes people do miss the obvious. Are you sure that the last cell in column G is not blank? If it is then the last row needs to be found on a column that will never have a blank cell in the last row. -- Regards, OssieMac "OssieMac" wrote: I haven't tested but try replacing following line LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row with this line LRow = .Cells(.Rows.Count, BldRngeWdth).End(xlUp).Row I have removed SH. Not needed when already using With SH. Placed . in front of Rows.Count because it belongs to SH If above does not work then place Stop after End With and then while stopped hover cursor over the various variables like LRow and rng.Address and check their values. -- Regards, OssieMac "FirstVette52" wrote: I need to set the print area for an ever changing range of rows (may start at row 200 on one execution, may start at Row 300 on the next), but no wider than Column G. The length of the Print Area is also ever changing, therefore I have used a formula that finds the last occupied row. I made some changes to the .Range () and now it no longer finds the last occupied row (finds all BUT the last row). IF 8 rows are occupied, THEN output is 7 rows. Can anyone see what I'm doing wrong? Sub Print_Preview() Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim BldRngeTop 'First cell in Print Area Dim BldRngeWdth 'Last Column in Print Area BldRngeTop = Range("Extract_Area_FirstCell").Address BldRngeWdth = "G" Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row Set rng = .Range(BldRngeTop & ":" & BldRngeWdth & LRow) .PageSetup.PrintArea = rng.Address End With ActiveWindow.SelectedSheets.PrintPreview End Sub Thanks for any help you might give. -- FirstVette52 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area
I just had one of those 'AHA!' moments wrapped-up in the realization that my
problem was a 'Senior' moment. Thanks Ossie, and you go right on pointing out the obvious, because sometimes I can't see the forest for the trees : ) -- FirstVette52 "OssieMac" wrote: I decided to test your code because I thought that it should work, particularly in view of the fact that you said it is returning one less row. Anyway your code works even though I still think that you should make my suggested changes . Therefore there is another problem. Please don't take offence at me for asking this but sometimes people do miss the obvious. Are you sure that the last cell in column G is not blank? If it is then the last row needs to be found on a column that will never have a blank cell in the last row. -- Regards, OssieMac "OssieMac" wrote: I haven't tested but try replacing following line LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row with this line LRow = .Cells(.Rows.Count, BldRngeWdth).End(xlUp).Row I have removed SH. Not needed when already using With SH. Placed . in front of Rows.Count because it belongs to SH If above does not work then place Stop after End With and then while stopped hover cursor over the various variables like LRow and rng.Address and check their values. -- Regards, OssieMac "FirstVette52" wrote: I need to set the print area for an ever changing range of rows (may start at row 200 on one execution, may start at Row 300 on the next), but no wider than Column G. The length of the Print Area is also ever changing, therefore I have used a formula that finds the last occupied row. I made some changes to the .Range () and now it no longer finds the last occupied row (finds all BUT the last row). IF 8 rows are occupied, THEN output is 7 rows. Can anyone see what I'm doing wrong? Sub Print_Preview() Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim BldRngeTop 'First cell in Print Area Dim BldRngeWdth 'Last Column in Print Area BldRngeTop = Range("Extract_Area_FirstCell").Address BldRngeWdth = "G" Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, BldRngeWdth).End(xlUp).Row Set rng = .Range(BldRngeTop & ":" & BldRngeWdth & LRow) .PageSetup.PrintArea = rng.Address End With ActiveWindow.SelectedSheets.PrintPreview End Sub Thanks for any help you might give. -- FirstVette52 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Print Area (ActiveSheet) | Excel Programming | |||
Dynamic Print Area | Excel Discussion (Misc queries) | |||
dynamic print area | Excel Worksheet Functions | |||
Vb to set print area for dynamic pages | Excel Programming | |||
Dynamic print area | Excel Programming |