VB list box print reports. Known # of columns. # rows unknown

[Richard]

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.

 

[JLGWhiz]

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

 

[JLGWhiz]

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.