This is fairly simply only that the sheet could be filtered and therefore data hidden. Excel can get a bit twitchy about things like that.
To Start...
Next you need to know how long the range is. You can do this by using the below lines of code.
4: With shtName
5: lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.....
18: End With
This line of code is a little life saver and is generally peppered throughout any automation project as finding the last row of data is generally rather important!
"shtName" is interchangeable for any name you want as long as it corresponds to the name of the worksheet you are fiddling with as is "C". Simply replace this with the relevant column in your case.
If you protect your worksheets then the line 6 on the completed listing below is needed as it can't calculate the range without removing that. If not you can remove the line as required.
Actual Printing...
The next section is the print set up and negates the need to show a preview, especially useful if you have hidden the ribbon to prevent unruly users playing with things they shouldn't be!
7: With .PageSetup
8: .Orientation = xlLandscape
9: .CenterHorizontally = True
10: .FitToPagesTall = 1
11: .FitToPagesWide = 1
12: .Zoom = 75
13: .PrintArea = Range("C6:N" & lastRow).SpecialCells(xlCellTypeVisible).address
14: End With
This section will change the printed page to landscape and centre the table alongside fitting it to the page in both height and width.
I set the zoom to 75% as this is what worked for my table, you can change this to whatever figure works best for you.
Setting the print area is basically the range you want to print. The first cell in my case being "C6" and the last in the predefined lastRow variable. We know the last column is "N" but we have no idea how long that column is hence the use of the lastRow variable.
Tagging on .SpecialCells(xlCellTypeVisible).address makes sure that if the data is filtered then only the visible cells are printed and Excel doesn't have a go at shoving the hidden data back into the table.
At this point you are ready to print and as there is no need of the preview button
15: Application.Dialogs(xlDialogPrint).Show
will show the print dialog box in Excel and allow you to change some of the printer settings but not the set up of the page.
If you wanted to see a print preview then simply substituting the line:
15.5: .PrintOut preview:=True
for the above would allow that. However if you have hidden the ribbon you can't do anything with the preview as the print button is also hidden.
Tidying up...
One thing I always found is that Excel leaves behind dotted lines in the print area showing how that range fits on the page. In an application this is unsightly and I'm not keen to be honest so I add the next line to satisfy the OCD and remove them.
16: .DisplayPageBreaks = False
Then if needs be simply lock the page remembering to allow for filtering again and get back to viewing your data.
Add this code to a button or within another procedure and you're good to go. You can also adapt it so that the range to print is added dynamically giving you more flexibility.
The Finished Article...
1: Public Function printRange()
2: Dim lastRow As Long
3:
4: With shtName
5: lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
6: .Unprotect "pwd"
7: With .PageSetup
8: .Orientation = xlLandscape
9: .CenterHorizontally = True
10: .FitToPagesTall = 1
11: .FitToPagesWide = 1
12: .Zoom = 75
13: .PrintArea = Range("C6:N" & lastRow).SpecialCells(xlCellTypeVisible).address
14: End With
15: Application.Dialogs(xlDialogPrint).Show
16: .DisplayPageBreaks = False
17: .Protect "pwd", AllowFiltering:=True
18: End With
19: End Function
