Prev: VBA code for VLOOKUP - linked to another workbook
Next: Macro Compatibility between 03 and 07
From: Richard on 4 Mar 2010 10:26
I have a Listbox with a number of reports. I know the number of columns in
the report but not the number of rows in the report. This will vary based on
data. How do I set the print area in VB when I don't know the number of rows.
From: JLGWhiz on 4 Mar 2010 11:07
One way, assuming your data begins in cell A1.

Set rng = ActiveSheet.Range(Cells(1, 1), _
Cells(UsedRange.Rows.Count, UsedRange.ColumnsCount))
ActiveSheet.PageSetUp.PrintArea = rng



"Richard" <Richard(a)discussions.microsoft.com> wrote in message
news:A660B32E-BEB0-4EFF-A146-0943407D4F91(a)microsoft.com...
>I have a Listbox with a number of reports. I know the number of columns in
> the report but not the number of rows in the report. This will vary based
> on
> data. How do I set the print area in VB when I don't know the number of
> rows.


From: JLGWhiz on 4 Mar 2010 13:11
P.S. Unless you have page breaks assigned in your worksheet, Excel will
normally query the printer interface and determine how to paginate the used
range. It will then print the entire printable area without the user having
to set a print range. You only have to be concerned if there is data
somewhere in a remote area of the worksheet that would cause unneeded blank
pages to be printed.


"JLGWhiz" <JLGWhiz(a)cfl.rr.com> wrote in message
news:e7yaIU7uKHA.5316(a)TK2MSFTNGP05.phx.gbl...
> One way, assuming your data begins in cell A1.
>
> Set rng = ActiveSheet.Range(Cells(1, 1), _
> Cells(UsedRange.Rows.Count, UsedRange.ColumnsCount))
> ActiveSheet.PageSetUp.PrintArea = rng
>
>
>
> "Richard" <Richard(a)discussions.microsoft.com> wrote in message
> news:A660B32E-BEB0-4EFF-A146-0943407D4F91(a)microsoft.com...
>>I have a Listbox with a number of reports. I know the number of columns in
>> the report but not the number of rows in the report. This will vary based
>> on
>> data. How do I set the print area in VB when I don't know the number of
>> rows.
>
>


 | 
Pages: 1
Prev: VBA code for VLOOKUP - linked to another workbook
Next: Macro Compatibility between 03 and 07