User Defined Print Area Macro

Curmit

New Member
Joined
Jun 17, 2008
Messages
5
After several unsuccessful in depth searches looking for the info required to complete the macro I am trying to write I have decided to try and post up my mission in hopes that someone with a lot more knowledge can help me out.

I am looking to create a macro that will ask the user to define the row number of the beginning and ending locations that they would like to print. The columns are fixed in that the first starting row will be from column A and the ending row will be column M.

I have built a pipe tally used in the oilfield that keeps track of large amounts of pipe figures but very often is the case that there is the need to print a section of the tally data in reference to a certain depth. With the vast knowledge base located here I was able to build the first macro that prints the whole active tally but now I need something more refined.

Below is one of the copies of the macro that I was trying to get to work and it is missing a couple of things but the most important part shows up and that is how to get the inputbox variables into the defined print area.

Sub TallyVariable()
Dim StartRow As String
Dim EndRow As String
StartRow = InputBox("Please Enter Starting Row you would like to print")
EndRow = InputBox("Please Enter Last Row you would like to print")
ActiveSheet.PageSetup.PrintArea = "A" & StartRow.Address:"M" & EndRow.Address
End Sub

Thanks in advance to anyone who might be able to help, I just got into using excel and most things I manage to figure out but this one is over my head.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
Remove Address here.
Code:
ActiveSheet.PageSetup.PrintArea = "A" & StartRow.Address:"M" & EndRow.Address
 
Upvote 0

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
It should be easier to allow the user to select the print area:

Code:
Sub PAreas()
Dim r As Range
Set r = Application.InputBox("Select print area", Type:=8)
If r Is Nothing Then Exit Sub
ActiveSheet.PageSetup.PrintArea = r.Address
End Sub

They can either enter an area or select it using the mouse.
 
Upvote 0

Curmit

New Member
Joined
Jun 17, 2008
Messages
5
Remove Address here.
Code:
ActiveSheet.PageSetup.PrintArea = "A" & StartRow.Address:"M" & EndRow.Address

Not sure I understand, I tried to remove the address like you said but it gives up an error each time.
 
Upvote 0

Curmit

New Member
Joined
Jun 17, 2008
Messages
5
It should be easier to allow the user to select the print area:

Code:
Sub PAreas()
Dim r As Range
Set r = Application.InputBox("Select print area", Type:=8)
If r Is Nothing Then Exit Sub
ActiveSheet.PageSetup.PrintArea = r.Address
End Sub

They can either enter an area or select it using the mouse.

It might seem easier but this has to be set up for people who do not know how to use a computer, having to enter a print range into a box would have to be explained in fine detail versus my approach of which row to start printing at and which row to end printing at.
 
Upvote 0

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
It might seem easier but this has to be set up for people who do not know how to use a computer, having to enter a print range into a box would have to be explained in fine detail versus my approach of which row to start printing at and which row to end printing at.

Can they not use a mouse to select a range of cells?
 
Upvote 0

Curmit

New Member
Joined
Jun 17, 2008
Messages
5
The data being selected can range from 10 lines to several hundred which would span several pages.
 
Upvote 0

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this

Code:
Sub TallyVariable()
Dim StartRow As String
Dim EndRow As String
StartRow = InputBox("Please Enter Starting Row you would like to print")
EndRow = InputBox("Please Enter Last Row you would like to print")
If StartRow = "" Or EndRow = "" Then Exit Sub
ActiveSheet.PageSetup.PrintArea = Range("A" & StartRow & ":M" & EndRow).Address
End Sub
 
Upvote 0

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Actually, this might be better (only one InputBox)

Code:
Sub TallyVariable()
Dim UserInput As String, X
UserInput = InputBox("Please enter Start and End rows that you would like to print, separated by a space e.g. 4 56")
If UserInput = "" Then Exit Sub
X = Split(UserInput)
If IsArray(X) Then
    ActiveSheet.PageSetup.PrintArea = Range("A" & X(LBound(X)) & ":M" & X(UBound(X))).Address
End If
End Sub
 
Upvote 0

Evagrius Ponticus

Well-known Member
Joined
May 24, 2007
Messages
1,467
VogII,

why does LBound(X) = 0 and UBound(X) = 1?

Does the split function separate 4 56 using " " as a dilineator?

Thanks for any explanation.
 
Upvote 0

Forum statistics

Threads
1,191,684
Messages
5,987,993
Members
440,124
Latest member
dippy_egg

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