Automatically updating Spreadsheet size based on a cell value

[DutchMonkey]

I have a spreadsheet that I am re-building for use with our customers. They enter in data parameters and we provide the quotation based upon what they provide.
However, their order sizes range from 2 - 20 all the way up to the hundreds and occasionally thousands.

I also use this as an instructional sheet for our assembly team. But in order to print I have to go through a series of "highlight area, print selection,..." etc. due to the hundreds of blank spaces that are being included in the initial print view.

Is there a way to automatically size the sheet when they enter the # of assemblies they are listing? The goal being to trim the "miles" of excess blank formatted pages without having to do it manually every time.

Caveat: I am mostly self taught with Excel and know the BARE MINIMUM on VBA so I will definitely need some guidance!

 

[maabadi]

Welcome to MrExcel Message Board.
Change F to your last column that you want Print. Try this :

Sub PrintArea()
Dim i As Long, Lr As Long

Lr = Range("A" & Rows.Count).End(xlUp).Row
Debug.Print Lr

ActiveSheet.PageSetup.PrintArea = Range("A1:F" & Lr).Address
ActiveSheet.PrintOut Preview:=True

End Sub

 

[DutchMonkey]

Welcome to MrExcel Message Board.
Change F to your last column that you want Print. Try this :

Sub PrintArea()
Dim i As Long, Lr As Long

Lr = Range("A" & Rows.Count).End(xlUp).Row
Debug.Print Lr

ActiveSheet.PageSetup.PrintArea = Range("A1:F" & Lr).Address
ActiveSheet.PrintOut Preview:=True

End Sub

maabadi,

I entered the code (once I found the vba screen lolz) and it does work to narrow the columns to what I need!
However, I am still encountering the issue of the multi page blanks. Here is snapshot of a scan that just printed.
Luckily I caught before it printed 11 more pages of the "blank area" at the bottom.

Is there a way to make the sheet itself stop at the "25" line or maybe one line after?
The "number of cables" cell is entered by the customer to designate how many lines are generated down the "Cable No." column.

 

[maabadi]

Try this. input lastrow for printing.

Sub PrintArea()
Dim i As Long, Lr As Long, K As Double
TryAgain:
    On Error GoTo ErrorHandler
    K = InputBox("Define Last row Number that you want Printed. ")
    If Len(K) = 0 Then
ErrorHandler:
      MsgBox "Please enter a valid value."
      GoTo TryAgain
        Exit Sub
    End If
ActiveSheet.PageSetup.PrintArea = Range("A1:F" & K).Address
ActiveSheet.PrintOut Preview:=True
End Sub

 

[maabadi]

Or Try this:

Sub PrintArea()
Dim i As Long, Lr As Long, K As Double
Dim userInput As Variant
Do
    userInput = InputBox("What is the amount purchased you would like to search for? ($)")
    If Len(userInput) = 0 Then Exit Sub
    If Not IsNumeric(userInput) Then MsgBox "Please enter a valid value."
Loop Until IsNumeric(userInput)
K = userInput
ActiveSheet.PageSetup.PrintArea = Range("A1:F" & K).Address
ActiveSheet.PrintOut Preview:=True
End Sub

 

[DutchMonkey]

Or Try this:

Sub PrintArea()
Dim i As Long, Lr As Long, K As Double
Dim userInput As Variant
Do
    userInput = InputBox("What is the amount purchased you would like to search for? ($)")
    If Len(userInput) = 0 Then Exit Sub
    If Not IsNumeric(userInput) Then MsgBox "Please enter a valid value."
Loop Until IsNumeric(userInput)
K = userInput
ActiveSheet.PageSetup.PrintArea = Range("A1:F" & K).Address
ActiveSheet.PrintOut Preview:=True
End Sub

Do you know if any solutions that will help grow the visible formatting?
I would like to reduce the "page size" of the file so that it does not think there are 100s of lines when it only needs about 30.

I am more targeting the on screen usage for this not necessarily the print option, but your scripts will very much help me for the hardcopy print steps thank you!

 

[maabadi]

Sorry. I don't noticed your reply
Try this:

Sub PrintArea()
Dim i As Long, Lr As Long, K As Double, j As Long
Dim userInput As Variant
Do
    userInput = InputBox("What is the amount purchased you would like to search for? ($)")
    If Len(userInput) = 0 Then Exit Sub
    If Not IsNumeric(userInput) Then MsgBox "Please enter a valid value."
Loop Until IsNumeric(userInput)
K = userInput

For I = 1 To K Step 30
    j = j + 1
    ActiveSheet.HPageBreaks.Add Range("A" & I + 1)
Next I
ActiveSheet.PageSetup.PrintArea = Range("A1:F" & K).Address
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = j
ActiveSheet.PrintOut Preview:=True
End Sub