VBA to have Input Box Determine Number of Rows to Include in Print Area with Offset and Header

[lamarh755]

I have a spreadsheet that I am trying to have an input box applied to vba coding that will enable me to enter a value in the input box that will determine the number of rows to print. Here are the specifics.

The Header starts at row 7 and goes from column A through column H.
The data starts at row 8.
When I enter 100 in the input box, I would like for the print area to capture the header from A7 to H7 and continue down 100 lines.

The current code that I have works but it starts counting the rows from row 1, so I only end up getting 93 lines of data when i enter 100 instead of the header plus 100 rows. Any assistance would be greatly appreciated.


.

 

[RunTime91]

I'm sure there are many solutions to this

But here is a simple one

With Ws
.PageSetup.PrintArea = .Range("A7:H" & numRows + 7).Address
End With

 

[RunTime91]

I'm sure there are many solutions to this

But here is a simple one

With Ws
.PageSetup.PrintArea = .Range("A7:H" & numRows + 7).Address
End With

I'm sure there are many solutions to this

But here is a simple one

With Ws
.PageSetup.PrintArea = .Range("A7:H" & numRows + 7).Address
End With

I'm not sure what happened above but here is another idea if you want to ditch the InputBox altogether.

Dim Wb As ThisWorkbook
Dim Ws As Worksheet

Set Ws = Wb.Sheets("YourSheetName")

Lr = Ws.Cells(Rows.Count, 1).End(xlUp).Row

With Ws
   .PageSetup.PrintArea = .Range("A7:H" & Lr).Address
End With

 

[lamarh755]

I'm sure there are many solutions to this

But here is a simple one

With Ws
.PageSetup.PrintArea = .Range("A7:H" & numRows + 7).Address
End With

This worked, thank you!

 

[lamarh755]

I'm not sure what happened above but here is another idea if you want to ditch the InputBox altogether.

Dim Wb As ThisWorkbook
Dim Ws As Worksheet

Set Ws = Wb.Sheets("YourSheetName")

Lr = Ws.Cells(Rows.Count, 1).End(xlUp).Row

With Ws
   .PageSetup.PrintArea = .Range("A7:H" & Lr).Address
End With

Thank you. I need the input box because I don't want to capture all of the lines in the spreadsheet, just a certain number that will change based on the amount of counts I think my team can complete each day. Thank you for providing both!