Automatically updating Spreadsheet size based on a cell value

DutchMonkey

New Member
Joined
Jan 19, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,776
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.
Change F to your last column that you want Print. Try this :
VBA Code:
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

New Member
Joined
Jan 19, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Welcome to MrExcel Message Board.
Change F to your last column that you want Print. Try this :
VBA Code:
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.
1611071912482.png

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

Well-known Member
Joined
Oct 22, 2012
Messages
1,776
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this. input lastrow for printing.
VBA Code:
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,776
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Or Try this:
VBA Code:
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

New Member
Joined
Jan 19, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Or Try this:
VBA Code:
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,776
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Sorry. I don't noticed your reply
Try this:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,404
Messages
5,636,091
Members
416,898
Latest member
imsorrymen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top