Excel set print area onclick

[DJ2018]

Hi guys I returning to this after 15 yrs out and my memory is poor. So any help would be fantastic.

I have a button to select a range and print.

Private Sub cmdbtnPrint_Click()
ActiveSheet.Select
Range("A3:F21").Select
ActiveSheet.PageSetup.PrintArea = "A3:F21"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True

End Sub

I need to have the end range as a variable ie F21 needs to look and find the last row to cell 21
Thanks

 

[dhookom]

I believe this might help faq707-2112.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

If your table is the ONLY thing on the sheet AND you properly DELETE to remove unused cells beside and beneath your table, then UsedRange can be used.

Alternativly, as long as you have a Proper Table (ONE row of headings, NO empty rows within your table, no other non-table data in cells contiguous with the table) then you can use CurrentRegion.

The latter would be my suggestion..

Private Sub cmdbtnPrint_Click()
ActiveSheet.Select
[b]Range("A3”).CurrentRegion.Select
ActiveSheet.PageSetup.PrintArea = Selection.Address[/b]
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DJ2018]

thanks guys